<!DOCTYPE html>
<html lang="en"><head><script src="/livereload.js?mindelay=10&amp;v=2&amp;port=1313&amp;path=livereload" data-no-instant defer></script>
  <meta charset="utf-8" />
  <meta name="viewport" content="width=device-width, initial-scale=1.0" />
  <meta name="robots" content="noindex, nofollow" />
  <link rel="icon shortcut" href="/favicon.ico" sizes="32x32" />
<link rel="icon" href="/favicon.svg" type="image/svg+xml" />
<link rel="icon" href="/favicon-dark.svg" type="image/svg+xml" media="(prefers-color-scheme: dark)" />
<link rel="icon" href="/favicon-16x16.png" type="image/png" sizes="16x16" />
<link rel="icon" href="/favicon-32x32.png" type="image/png" sizes="32x32" />
<link rel="apple-touch-icon" href="/apple-touch-icon.png" sizes="180x180" />
<link fetchpriority="low" href="/site.webmanifest" rel="manifest" />

  <title>第 22 章：哈希 – PostgreSQL 14 Internals</title>
  <meta name="description" content="22.1 哈希连接 22.1.1 一阶段哈希连接 哈希连接使用预建的哈希表搜索匹配的行。以下是包含这种连接的计划示例：
=&gt; EXPLAIN (costs off) SELECT * FROM tickets t JOIN ticket_flights tf ON tf.ticket_no = t.ticket_no; QUERY PLAN −−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−− Hash Join Hash Cond: (tf.ticket_no = t.ticket_no) −&gt; Seq Scan on ticket_flights tf −&gt; Hash −&gt; Seq Scan on tickets t (5 rows) 在第一阶段，Hash Join 节点 1 调用 Hash 节点 2，后者从其子节点取出整个内层数据集并将其放入哈希表中。" />

  
    <link rel="canonical" href="//localhost:1313/docs/chapter22/" itemprop="url" />
  

  

<meta property="og:title" content="第 22 章：哈希" />
<meta property="og:description" content="" />
<meta property="og:type" content="website" />
<meta property="og:url" content="//localhost:1313/docs/chapter22/" />

  
  <meta itemprop="name" content="第 22 章：哈希">
  <meta itemprop="description" content="22.1 哈希连接 22.1.1 一阶段哈希连接 哈希连接使用预建的哈希表搜索匹配的行。以下是包含这种连接的计划示例：
=&gt; EXPLAIN (costs off) SELECT * FROM tickets t JOIN ticket_flights tf ON tf.ticket_no = t.ticket_no; QUERY PLAN −−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−− Hash Join Hash Cond: (tf.ticket_no = t.ticket_no) −&gt; Seq Scan on ticket_flights tf −&gt; Hash −&gt; Seq Scan on tickets t (5 rows) 在第一阶段，Hash Join 节点 1 调用 Hash 节点 2，后者从其子节点取出整个内层数据集并将其放入哈希表中。">
  <meta itemprop="wordCount" content="1637">
  <meta name="twitter:card" content="summary">
  <meta name="twitter:title" content="第 22 章：哈希">
  <meta name="twitter:description" content="22.1 哈希连接 22.1.1 一阶段哈希连接 哈希连接使用预建的哈希表搜索匹配的行。以下是包含这种连接的计划示例：
=&gt; EXPLAIN (costs off) SELECT * FROM tickets t JOIN ticket_flights tf ON tf.ticket_no = t.ticket_no; QUERY PLAN −−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−− Hash Join Hash Cond: (tf.ticket_no = t.ticket_no) −&gt; Seq Scan on ticket_flights tf −&gt; Hash −&gt; Seq Scan on tickets t (5 rows) 在第一阶段，Hash Join 节点 1 调用 Hash 节点 2，后者从其子节点取出整个内层数据集并将其放入哈希表中。">

    <link href="/css/compiled/main.css" rel="stylesheet" />



  <link href="/css/custom.css" rel="stylesheet" />





  <script>
     
    const defaultTheme = 'light';

    const setDarkTheme = () => {
      document.documentElement.classList.add("dark");
      document.documentElement.style.colorScheme = "dark";
    }
    const setLightTheme = () => {
      document.documentElement.classList.remove("dark");
      document.documentElement.style.colorScheme = "light";
    }

    if ("color-theme" in localStorage) {
      localStorage.getItem("color-theme") === "dark" ? setDarkTheme() : setLightTheme();
    } else {
      defaultTheme === "dark" ? setDarkTheme() : setLightTheme();
      if (defaultTheme === "system") {
        window.matchMedia("(prefers-color-scheme: dark)").matches ? setDarkTheme() : setLightTheme();
      }
    }
  </script>

  
</head>
<body dir="ltr"><div class="nav-container hx-sticky hx-top-0 hx-z-20 hx-w-full hx-bg-transparent print:hx-hidden">
  <div class="nav-container-blur hx-pointer-events-none hx-absolute hx-z-[-1] hx-h-full hx-w-full hx-bg-white dark:hx-bg-dark hx-shadow-[0_2px_4px_rgba(0,0,0,.02),0_1px_0_rgba(0,0,0,.06)] contrast-more:hx-shadow-[0_0_0_1px_#000] dark:hx-shadow-[0_-1px_0_rgba(255,255,255,.1)_inset] contrast-more:dark:hx-shadow-[0_0_0_1px_#fff]"></div>

  <nav class="hx-mx-auto hx-flex hx-items-center hx-justify-end hx-gap-2 hx-h-16 hx-px-6 hx-max-w-[90rem]">
    <a class="hx-flex hx-items-center hover:hx-opacity-75 ltr:hx-mr-auto rtl:hx-ml-auto" href="/">
        <img class="hx-block dark:hx-hidden" src="/images/postgresql-elephant-in-potential.svg" alt="PostgreSQL 14 Internals" height="30" width="45" />
        <img class="hx-hidden dark:hx-block" src="/images/postgresql-elephant-in-power.svg" alt="PostgreSQL 14 Internals" height="30" width="45" />
        <span class="hx-mx-2 hx-font-extrabold hx-inline hx-select-none" title="PostgreSQL 14 Internals">PostgreSQL 14 Internals</span>
    </a><a
            title="Docs"
            href="/docs"
            
            class="hx-text-sm contrast-more:hx-text-gray-700 contrast-more:dark:hx-text-gray-100 hx-relative -hx-ml-2 hx-hidden hx-whitespace-nowrap hx-p-2 md:hx-inline-block hx-font-medium"
          >
            <span class="hx-text-center">Docs</span>
          </a><a
            title="About"
            href="/about"
            
            class="hx-text-sm contrast-more:hx-text-gray-700 contrast-more:dark:hx-text-gray-100 hx-relative -hx-ml-2 hx-hidden hx-whitespace-nowrap hx-p-2 md:hx-inline-block hx-text-gray-600 hover:hx-text-gray-800 dark:hx-text-gray-400 dark:hover:hx-text-gray-200"
          >
            <span class="hx-text-center">About</span>
          </a><a
            title="Contact ↗"
            href="https://github.com/xiongcccc"
            target="_blank" rel="noreferer"
            class="hx-text-sm contrast-more:hx-text-gray-700 contrast-more:dark:hx-text-gray-100 hx-relative -hx-ml-2 hx-hidden hx-whitespace-nowrap hx-p-2 md:hx-inline-block hx-text-gray-600 hover:hx-text-gray-800 dark:hx-text-gray-400 dark:hover:hx-text-gray-200"
          >
            <span class="hx-text-center">Contact ↗</span>
          </a><div class="search-wrapper hx-relative md:hx-w-64">
  <div class="hx-relative hx-flex hx-items-center hx-text-gray-900 contrast-more:hx-text-gray-800 dark:hx-text-gray-300 contrast-more:dark:hx-text-gray-300">
    <input
      placeholder="Search..."
      class="search-input hx-block hx-w-full hx-appearance-none hx-rounded-lg hx-px-3 hx-py-2 hx-transition-colors hx-text-base hx-leading-tight md:hx-text-sm hx-bg-black/[.05] dark:hx-bg-gray-50/10 focus:hx-bg-white dark:focus:hx-bg-dark placeholder:hx-text-gray-500 dark:placeholder:hx-text-gray-400 contrast-more:hx-border contrast-more:hx-border-current"
      type="search"
      value=""
      spellcheck="false"
    />
    <kbd
      class="hx-absolute hx-my-1.5 hx-select-none ltr:hx-right-1.5 rtl:hx-left-1.5 hx-h-5 hx-rounded hx-bg-white hx-px-1.5 hx-font-mono hx-text-[10px] hx-font-medium hx-text-gray-500 hx-border dark:hx-border-gray-100/20 dark:hx-bg-dark/50 contrast-more:hx-border-current contrast-more:hx-text-current contrast-more:dark:hx-border-current hx-items-center hx-gap-1 hx-transition-opacity hx-pointer-events-none hx-hidden sm:hx-flex"
    >
      CTRL K
    </kbd>
  </div>

  <div>
    <ul
      class="search-results hextra-scrollbar hx-hidden hx-border hx-border-gray-200 hx-bg-white hx-text-gray-100 dark:hx-border-neutral-800 dark:hx-bg-neutral-900 hx-absolute hx-top-full hx-z-20 hx-mt-2 hx-overflow-auto hx-overscroll-contain hx-rounded-xl hx-py-2.5 hx-shadow-xl hx-max-h-[min(calc(50vh-11rem-env(safe-area-inset-bottom)),400px)] md:hx-max-h-[min(calc(100vh-5rem-env(safe-area-inset-bottom)),400px)] hx-inset-x-0 ltr:md:hx-left-auto rtl:md:hx-right-auto contrast-more:hx-border contrast-more:hx-border-gray-900 contrast-more:dark:hx-border-gray-50 hx-w-screen hx-min-h-[100px] hx-max-w-[min(calc(100vw-2rem),calc(100%+20rem))]"
      style="transition: max-height 0.2s ease 0s;"
    ></ul>
  </div>
</div>

          <a class="hx-p-2 hx-text-current" target="_blank" rel="noreferer" href="https://github.com/xiongcccc/xiongcccc.github.io" title="GitHub"><svg height=24 fill="currentColor" viewBox="3 3 18 18">
  <path d="M12 3C7.0275 3 3 7.12937 3 12.2276C3 16.3109 5.57625 19.7597 9.15374 20.9824C9.60374 21.0631 9.77249 20.7863 9.77249 20.5441C9.77249 20.3249 9.76125 19.5982 9.76125 18.8254C7.5 19.2522 6.915 18.2602 6.735 17.7412C6.63375 17.4759 6.19499 16.6569 5.8125 16.4378C5.4975 16.2647 5.0475 15.838 5.80124 15.8264C6.51 15.8149 7.01625 16.4954 7.18499 16.7723C7.99499 18.1679 9.28875 17.7758 9.80625 17.5335C9.885 16.9337 10.1212 16.53 10.38 16.2993C8.3775 16.0687 6.285 15.2728 6.285 11.7432C6.285 10.7397 6.63375 9.9092 7.20749 9.26326C7.1175 9.03257 6.8025 8.08674 7.2975 6.81794C7.2975 6.81794 8.05125 6.57571 9.77249 7.76377C10.4925 7.55615 11.2575 7.45234 12.0225 7.45234C12.7875 7.45234 13.5525 7.55615 14.2725 7.76377C15.9937 6.56418 16.7475 6.81794 16.7475 6.81794C17.2424 8.08674 16.9275 9.03257 16.8375 9.26326C17.4113 9.9092 17.76 10.7281 17.76 11.7432C17.76 15.2843 15.6563 16.0687 13.6537 16.2993C13.98 16.5877 14.2613 17.1414 14.2613 18.0065C14.2613 19.2407 14.25 20.2326 14.25 20.5441C14.25 20.7863 14.4188 21.0746 14.8688 20.9824C16.6554 20.364 18.2079 19.1866 19.3078 17.6162C20.4077 16.0457 20.9995 14.1611 21 12.2276C21 7.12937 16.9725 3 12 3Z"></path>
</svg>
<span class="hx-sr-only">GitHub</span>
          </a>
          <a class="hx-p-2 hx-text-current" target="_blank" rel="noreferer" href="https://twitter.com/" title="Twitter"><svg height=24 xmlns="http://www.w3.org/2000/svg" viewBox="0 0 512 512"><path fill="currentColor" d="M389.2 48h70.6L305.6 224.2 487 464H345L233.7 318.6 106.5 464H35.8L200.7 275.5 26.8 48H172.4L272.9 180.9 389.2 48zM364.4 421.8h39.1L151.1 88h-42L364.4 421.8z"/></svg><span class="hx-sr-only">Twitter</span>
          </a><button type="button" aria-label="Menu" class="hamburger-menu -hx-mr-2 hx-rounded hx-p-2 active:hx-bg-gray-400/20 md:hx-hidden"><svg height=24 fill="none" viewBox="0 0 24 24" stroke="currentColor"><g><path stroke-linecap="round" stroke-linejoin="round" stroke-width="2" d="M4 8H20"></path></g><g><path stroke-linecap="round" stroke-linejoin="round" stroke-width="2" d="M4 16H20"></path></g></svg></button>
  </nav>
</div>

  <div class='hx-mx-auto hx-flex hx-max-w-[90rem]'>
    <div class="mobile-menu-overlay [transition:background-color_1.5s_ease] hx-fixed hx-inset-0 hx-z-10 hx-bg-black/80 dark:hx-bg-black/60 hx-hidden"></div>
<aside class="sidebar-container hx-flex hx-flex-col print:hx-hidden md:hx-top-16 md:hx-shrink-0 md:hx-w-64 md:hx-self-start max-md:[transform:translate3d(0,-100%,0)] md:hx-sticky">
  
  <div class="hx-px-4 hx-pt-4 md:hx-hidden">
    <div class="search-wrapper hx-relative md:hx-w-64">
  <div class="hx-relative hx-flex hx-items-center hx-text-gray-900 contrast-more:hx-text-gray-800 dark:hx-text-gray-300 contrast-more:dark:hx-text-gray-300">
    <input
      placeholder="Search..."
      class="search-input hx-block hx-w-full hx-appearance-none hx-rounded-lg hx-px-3 hx-py-2 hx-transition-colors hx-text-base hx-leading-tight md:hx-text-sm hx-bg-black/[.05] dark:hx-bg-gray-50/10 focus:hx-bg-white dark:focus:hx-bg-dark placeholder:hx-text-gray-500 dark:placeholder:hx-text-gray-400 contrast-more:hx-border contrast-more:hx-border-current"
      type="search"
      value=""
      spellcheck="false"
    />
    <kbd
      class="hx-absolute hx-my-1.5 hx-select-none ltr:hx-right-1.5 rtl:hx-left-1.5 hx-h-5 hx-rounded hx-bg-white hx-px-1.5 hx-font-mono hx-text-[10px] hx-font-medium hx-text-gray-500 hx-border dark:hx-border-gray-100/20 dark:hx-bg-dark/50 contrast-more:hx-border-current contrast-more:hx-text-current contrast-more:dark:hx-border-current hx-items-center hx-gap-1 hx-transition-opacity hx-pointer-events-none hx-hidden sm:hx-flex"
    >
      CTRL K
    </kbd>
  </div>

  <div>
    <ul
      class="search-results hextra-scrollbar hx-hidden hx-border hx-border-gray-200 hx-bg-white hx-text-gray-100 dark:hx-border-neutral-800 dark:hx-bg-neutral-900 hx-absolute hx-top-full hx-z-20 hx-mt-2 hx-overflow-auto hx-overscroll-contain hx-rounded-xl hx-py-2.5 hx-shadow-xl hx-max-h-[min(calc(50vh-11rem-env(safe-area-inset-bottom)),400px)] md:hx-max-h-[min(calc(100vh-5rem-env(safe-area-inset-bottom)),400px)] hx-inset-x-0 ltr:md:hx-left-auto rtl:md:hx-right-auto contrast-more:hx-border contrast-more:hx-border-gray-900 contrast-more:dark:hx-border-gray-50 hx-w-screen hx-min-h-[100px] hx-max-w-[min(calc(100vw-2rem),calc(100%+20rem))]"
      style="transition: max-height 0.2s ease 0s;"
    ></ul>
  </div>
</div>

  </div>
  <div class="hextra-scrollbar hx-overflow-y-auto hx-overflow-x-hidden hx-p-4 hx-grow md:hx-h-[calc(100vh-var(--navbar-height)-var(--menu-height))]">
    <ul class="hx-flex hx-flex-col hx-gap-1 md:hx-hidden">
      
      
          <li class=""><a
    class="hx-flex hx-items-center hx-justify-between hx-gap-2 hx-cursor-pointer hx-rounded hx-px-2 hx-py-1.5 hx-text-sm hx-transition-colors [-webkit-tap-highlight-color:transparent] [-webkit-touch-callout:none] [word-break:break-word]
      hx-text-gray-500 hover:hx-bg-gray-100 hover:hx-text-gray-900 contrast-more:hx-border contrast-more:hx-border-transparent contrast-more:hx-text-gray-900 contrast-more:hover:hx-border-gray-900 dark:hx-text-neutral-400 dark:hover:hx-bg-primary-100/5 dark:hover:hx-text-gray-50 contrast-more:dark:hx-text-gray-50 contrast-more:dark:hover:hx-border-gray-50"
    href="/about/"
    
  >About
    </a></li>
          <li class="open"><a
    class="hx-flex hx-items-center hx-justify-between hx-gap-2 hx-cursor-pointer hx-rounded hx-px-2 hx-py-1.5 hx-text-sm hx-transition-colors [-webkit-tap-highlight-color:transparent] [-webkit-touch-callout:none] [word-break:break-word]
      hx-text-gray-500 hover:hx-bg-gray-100 hover:hx-text-gray-900 contrast-more:hx-border contrast-more:hx-border-transparent contrast-more:hx-text-gray-900 contrast-more:hover:hx-border-gray-900 dark:hx-text-neutral-400 dark:hover:hx-bg-primary-100/5 dark:hover:hx-text-gray-50 contrast-more:dark:hx-text-gray-50 contrast-more:dark:hover:hx-border-gray-50"
    href="/docs/"
    
  >Docs
        <span class="hextra-sidebar-collapsible-button"><svg fill="none" viewBox="0 0 24 24" stroke="currentColor" class="hx-h-[18px] hx-min-w-[18px] hx-rounded-sm hx-p-0.5 hover:hx-bg-gray-800/5 dark:hover:hx-bg-gray-100/5"><path stroke-linecap="round" stroke-linejoin="round" stroke-width="2" d="M9 5l7 7-7 7" class="hx-origin-center hx-transition-transform rtl:-hx-rotate-180"></path></svg></span>
    </a><div class="ltr:hx-pr-0 hx-overflow-hidden">
        <ul class='hx-relative hx-flex hx-flex-col hx-gap-1 before:hx-absolute before:hx-inset-y-1 before:hx-w-px before:hx-bg-gray-200 before:hx-content-[""] ltr:hx-ml-3 ltr:hx-pl-3 ltr:before:hx-left-0 rtl:hx-mr-3 rtl:hx-pr-3 rtl:before:hx-right-0 dark:before:hx-bg-neutral-800'><li class="hx-flex hx-flex-col open"><a
    class="hx-flex hx-items-center hx-justify-between hx-gap-2 hx-cursor-pointer hx-rounded hx-px-2 hx-py-1.5 hx-text-sm hx-transition-colors [-webkit-tap-highlight-color:transparent] [-webkit-touch-callout:none] [word-break:break-word]
      hx-text-gray-500 hover:hx-bg-gray-100 hover:hx-text-gray-900 contrast-more:hx-border contrast-more:hx-border-transparent contrast-more:hx-text-gray-900 contrast-more:hover:hx-border-gray-900 dark:hx-text-neutral-400 dark:hover:hx-bg-primary-100/5 dark:hover:hx-text-gray-50 contrast-more:dark:hx-text-gray-50 contrast-more:dark:hover:hx-border-gray-50"
    href="/docs/chapter01/"
    
  >第 1 章：介绍
    </a>
              
            </li><li class="hx-flex hx-flex-col open"><a
    class="hx-flex hx-items-center hx-justify-between hx-gap-2 hx-cursor-pointer hx-rounded hx-px-2 hx-py-1.5 hx-text-sm hx-transition-colors [-webkit-tap-highlight-color:transparent] [-webkit-touch-callout:none] [word-break:break-word]
      hx-text-gray-500 hover:hx-bg-gray-100 hover:hx-text-gray-900 contrast-more:hx-border contrast-more:hx-border-transparent contrast-more:hx-text-gray-900 contrast-more:hover:hx-border-gray-900 dark:hx-text-neutral-400 dark:hover:hx-bg-primary-100/5 dark:hover:hx-text-gray-50 contrast-more:dark:hx-text-gray-50 contrast-more:dark:hover:hx-border-gray-50"
    href="/docs/chapter02/"
    
  >第 2 章：隔离性
    </a>
              
            </li><li class="hx-flex hx-flex-col open"><a
    class="hx-flex hx-items-center hx-justify-between hx-gap-2 hx-cursor-pointer hx-rounded hx-px-2 hx-py-1.5 hx-text-sm hx-transition-colors [-webkit-tap-highlight-color:transparent] [-webkit-touch-callout:none] [word-break:break-word]
      hx-text-gray-500 hover:hx-bg-gray-100 hover:hx-text-gray-900 contrast-more:hx-border contrast-more:hx-border-transparent contrast-more:hx-text-gray-900 contrast-more:hover:hx-border-gray-900 dark:hx-text-neutral-400 dark:hover:hx-bg-primary-100/5 dark:hover:hx-text-gray-50 contrast-more:dark:hx-text-gray-50 contrast-more:dark:hover:hx-border-gray-50"
    href="/docs/chapter03/"
    
  >第 3 章：页与元组
    </a>
              
            </li><li class="hx-flex hx-flex-col open"><a
    class="hx-flex hx-items-center hx-justify-between hx-gap-2 hx-cursor-pointer hx-rounded hx-px-2 hx-py-1.5 hx-text-sm hx-transition-colors [-webkit-tap-highlight-color:transparent] [-webkit-touch-callout:none] [word-break:break-word]
      hx-text-gray-500 hover:hx-bg-gray-100 hover:hx-text-gray-900 contrast-more:hx-border contrast-more:hx-border-transparent contrast-more:hx-text-gray-900 contrast-more:hover:hx-border-gray-900 dark:hx-text-neutral-400 dark:hover:hx-bg-primary-100/5 dark:hover:hx-text-gray-50 contrast-more:dark:hx-text-gray-50 contrast-more:dark:hover:hx-border-gray-50"
    href="/docs/chapter04/"
    
  >第 4 章：快照
    </a>
              
            </li><li class="hx-flex hx-flex-col open"><a
    class="hx-flex hx-items-center hx-justify-between hx-gap-2 hx-cursor-pointer hx-rounded hx-px-2 hx-py-1.5 hx-text-sm hx-transition-colors [-webkit-tap-highlight-color:transparent] [-webkit-touch-callout:none] [word-break:break-word]
      hx-text-gray-500 hover:hx-bg-gray-100 hover:hx-text-gray-900 contrast-more:hx-border contrast-more:hx-border-transparent contrast-more:hx-text-gray-900 contrast-more:hover:hx-border-gray-900 dark:hx-text-neutral-400 dark:hover:hx-bg-primary-100/5 dark:hover:hx-text-gray-50 contrast-more:dark:hx-text-gray-50 contrast-more:dark:hover:hx-border-gray-50"
    href="/docs/chapter05/"
    
  >第 5 章：页剪枝与 HOT 更新
    </a>
              
            </li><li class="hx-flex hx-flex-col open"><a
    class="hx-flex hx-items-center hx-justify-between hx-gap-2 hx-cursor-pointer hx-rounded hx-px-2 hx-py-1.5 hx-text-sm hx-transition-colors [-webkit-tap-highlight-color:transparent] [-webkit-touch-callout:none] [word-break:break-word]
      hx-text-gray-500 hover:hx-bg-gray-100 hover:hx-text-gray-900 contrast-more:hx-border contrast-more:hx-border-transparent contrast-more:hx-text-gray-900 contrast-more:hover:hx-border-gray-900 dark:hx-text-neutral-400 dark:hover:hx-bg-primary-100/5 dark:hover:hx-text-gray-50 contrast-more:dark:hx-text-gray-50 contrast-more:dark:hover:hx-border-gray-50"
    href="/docs/chapter06/"
    
  >第 6 章：Vacuum 与 Autovacuum
    </a>
              
            </li><li class="hx-flex hx-flex-col open"><a
    class="hx-flex hx-items-center hx-justify-between hx-gap-2 hx-cursor-pointer hx-rounded hx-px-2 hx-py-1.5 hx-text-sm hx-transition-colors [-webkit-tap-highlight-color:transparent] [-webkit-touch-callout:none] [word-break:break-word]
      hx-text-gray-500 hover:hx-bg-gray-100 hover:hx-text-gray-900 contrast-more:hx-border contrast-more:hx-border-transparent contrast-more:hx-text-gray-900 contrast-more:hover:hx-border-gray-900 dark:hx-text-neutral-400 dark:hover:hx-bg-primary-100/5 dark:hover:hx-text-gray-50 contrast-more:dark:hx-text-gray-50 contrast-more:dark:hover:hx-border-gray-50"
    href="/docs/chapter07/"
    
  >第 7 章：冻结
    </a>
              
            </li><li class="hx-flex hx-flex-col open"><a
    class="hx-flex hx-items-center hx-justify-between hx-gap-2 hx-cursor-pointer hx-rounded hx-px-2 hx-py-1.5 hx-text-sm hx-transition-colors [-webkit-tap-highlight-color:transparent] [-webkit-touch-callout:none] [word-break:break-word]
      hx-text-gray-500 hover:hx-bg-gray-100 hover:hx-text-gray-900 contrast-more:hx-border contrast-more:hx-border-transparent contrast-more:hx-text-gray-900 contrast-more:hover:hx-border-gray-900 dark:hx-text-neutral-400 dark:hover:hx-bg-primary-100/5 dark:hover:hx-text-gray-50 contrast-more:dark:hx-text-gray-50 contrast-more:dark:hover:hx-border-gray-50"
    href="/docs/chapter08/"
    
  >第 8 章：重建表与索引
    </a>
              
            </li><li class="hx-flex hx-flex-col open"><a
    class="hx-flex hx-items-center hx-justify-between hx-gap-2 hx-cursor-pointer hx-rounded hx-px-2 hx-py-1.5 hx-text-sm hx-transition-colors [-webkit-tap-highlight-color:transparent] [-webkit-touch-callout:none] [word-break:break-word]
      hx-text-gray-500 hover:hx-bg-gray-100 hover:hx-text-gray-900 contrast-more:hx-border contrast-more:hx-border-transparent contrast-more:hx-text-gray-900 contrast-more:hover:hx-border-gray-900 dark:hx-text-neutral-400 dark:hover:hx-bg-primary-100/5 dark:hover:hx-text-gray-50 contrast-more:dark:hx-text-gray-50 contrast-more:dark:hover:hx-border-gray-50"
    href="/docs/chapter09/"
    
  >第 9 章：缓冲区缓存
    </a>
              
            </li><li class="hx-flex hx-flex-col open"><a
    class="hx-flex hx-items-center hx-justify-between hx-gap-2 hx-cursor-pointer hx-rounded hx-px-2 hx-py-1.5 hx-text-sm hx-transition-colors [-webkit-tap-highlight-color:transparent] [-webkit-touch-callout:none] [word-break:break-word]
      hx-text-gray-500 hover:hx-bg-gray-100 hover:hx-text-gray-900 contrast-more:hx-border contrast-more:hx-border-transparent contrast-more:hx-text-gray-900 contrast-more:hover:hx-border-gray-900 dark:hx-text-neutral-400 dark:hover:hx-bg-primary-100/5 dark:hover:hx-text-gray-50 contrast-more:dark:hx-text-gray-50 contrast-more:dark:hover:hx-border-gray-50"
    href="/docs/chapter10/"
    
  >第 10 章：预写式日志
    </a>
              
            </li><li class="hx-flex hx-flex-col open"><a
    class="hx-flex hx-items-center hx-justify-between hx-gap-2 hx-cursor-pointer hx-rounded hx-px-2 hx-py-1.5 hx-text-sm hx-transition-colors [-webkit-tap-highlight-color:transparent] [-webkit-touch-callout:none] [word-break:break-word]
      hx-text-gray-500 hover:hx-bg-gray-100 hover:hx-text-gray-900 contrast-more:hx-border contrast-more:hx-border-transparent contrast-more:hx-text-gray-900 contrast-more:hover:hx-border-gray-900 dark:hx-text-neutral-400 dark:hover:hx-bg-primary-100/5 dark:hover:hx-text-gray-50 contrast-more:dark:hx-text-gray-50 contrast-more:dark:hover:hx-border-gray-50"
    href="/docs/chapter11/"
    
  >第 11 章：WAL 模式
    </a>
              
            </li><li class="hx-flex hx-flex-col open"><a
    class="hx-flex hx-items-center hx-justify-between hx-gap-2 hx-cursor-pointer hx-rounded hx-px-2 hx-py-1.5 hx-text-sm hx-transition-colors [-webkit-tap-highlight-color:transparent] [-webkit-touch-callout:none] [word-break:break-word]
      hx-text-gray-500 hover:hx-bg-gray-100 hover:hx-text-gray-900 contrast-more:hx-border contrast-more:hx-border-transparent contrast-more:hx-text-gray-900 contrast-more:hover:hx-border-gray-900 dark:hx-text-neutral-400 dark:hover:hx-bg-primary-100/5 dark:hover:hx-text-gray-50 contrast-more:dark:hx-text-gray-50 contrast-more:dark:hover:hx-border-gray-50"
    href="/docs/chapter12/"
    
  >第 12 章：关系级锁
    </a>
              
            </li><li class="hx-flex hx-flex-col open"><a
    class="hx-flex hx-items-center hx-justify-between hx-gap-2 hx-cursor-pointer hx-rounded hx-px-2 hx-py-1.5 hx-text-sm hx-transition-colors [-webkit-tap-highlight-color:transparent] [-webkit-touch-callout:none] [word-break:break-word]
      hx-text-gray-500 hover:hx-bg-gray-100 hover:hx-text-gray-900 contrast-more:hx-border contrast-more:hx-border-transparent contrast-more:hx-text-gray-900 contrast-more:hover:hx-border-gray-900 dark:hx-text-neutral-400 dark:hover:hx-bg-primary-100/5 dark:hover:hx-text-gray-50 contrast-more:dark:hx-text-gray-50 contrast-more:dark:hover:hx-border-gray-50"
    href="/docs/chapter13/"
    
  >第 13 章：行级锁
    </a>
              
            </li><li class="hx-flex hx-flex-col open"><a
    class="hx-flex hx-items-center hx-justify-between hx-gap-2 hx-cursor-pointer hx-rounded hx-px-2 hx-py-1.5 hx-text-sm hx-transition-colors [-webkit-tap-highlight-color:transparent] [-webkit-touch-callout:none] [word-break:break-word]
      hx-text-gray-500 hover:hx-bg-gray-100 hover:hx-text-gray-900 contrast-more:hx-border contrast-more:hx-border-transparent contrast-more:hx-text-gray-900 contrast-more:hover:hx-border-gray-900 dark:hx-text-neutral-400 dark:hover:hx-bg-primary-100/5 dark:hover:hx-text-gray-50 contrast-more:dark:hx-text-gray-50 contrast-more:dark:hover:hx-border-gray-50"
    href="/docs/chapter14/"
    
  >第 14 章：多样的锁
    </a>
              
            </li><li class="hx-flex hx-flex-col open"><a
    class="hx-flex hx-items-center hx-justify-between hx-gap-2 hx-cursor-pointer hx-rounded hx-px-2 hx-py-1.5 hx-text-sm hx-transition-colors [-webkit-tap-highlight-color:transparent] [-webkit-touch-callout:none] [word-break:break-word]
      hx-text-gray-500 hover:hx-bg-gray-100 hover:hx-text-gray-900 contrast-more:hx-border contrast-more:hx-border-transparent contrast-more:hx-text-gray-900 contrast-more:hover:hx-border-gray-900 dark:hx-text-neutral-400 dark:hover:hx-bg-primary-100/5 dark:hover:hx-text-gray-50 contrast-more:dark:hx-text-gray-50 contrast-more:dark:hover:hx-border-gray-50"
    href="/docs/chapter15/"
    
  >第 15 章：内存结构上的锁
    </a>
              
            </li><li class="hx-flex hx-flex-col open"><a
    class="hx-flex hx-items-center hx-justify-between hx-gap-2 hx-cursor-pointer hx-rounded hx-px-2 hx-py-1.5 hx-text-sm hx-transition-colors [-webkit-tap-highlight-color:transparent] [-webkit-touch-callout:none] [word-break:break-word]
      hx-text-gray-500 hover:hx-bg-gray-100 hover:hx-text-gray-900 contrast-more:hx-border contrast-more:hx-border-transparent contrast-more:hx-text-gray-900 contrast-more:hover:hx-border-gray-900 dark:hx-text-neutral-400 dark:hover:hx-bg-primary-100/5 dark:hover:hx-text-gray-50 contrast-more:dark:hx-text-gray-50 contrast-more:dark:hover:hx-border-gray-50"
    href="/docs/chapter16/"
    
  >第 16 章：查询执行阶段
    </a>
              
            </li><li class="hx-flex hx-flex-col open"><a
    class="hx-flex hx-items-center hx-justify-between hx-gap-2 hx-cursor-pointer hx-rounded hx-px-2 hx-py-1.5 hx-text-sm hx-transition-colors [-webkit-tap-highlight-color:transparent] [-webkit-touch-callout:none] [word-break:break-word]
      hx-text-gray-500 hover:hx-bg-gray-100 hover:hx-text-gray-900 contrast-more:hx-border contrast-more:hx-border-transparent contrast-more:hx-text-gray-900 contrast-more:hover:hx-border-gray-900 dark:hx-text-neutral-400 dark:hover:hx-bg-primary-100/5 dark:hover:hx-text-gray-50 contrast-more:dark:hx-text-gray-50 contrast-more:dark:hover:hx-border-gray-50"
    href="/docs/chapter17/"
    
  >第 17 章：统计信息
    </a>
              
            </li><li class="hx-flex hx-flex-col open"><a
    class="hx-flex hx-items-center hx-justify-between hx-gap-2 hx-cursor-pointer hx-rounded hx-px-2 hx-py-1.5 hx-text-sm hx-transition-colors [-webkit-tap-highlight-color:transparent] [-webkit-touch-callout:none] [word-break:break-word]
      hx-text-gray-500 hover:hx-bg-gray-100 hover:hx-text-gray-900 contrast-more:hx-border contrast-more:hx-border-transparent contrast-more:hx-text-gray-900 contrast-more:hover:hx-border-gray-900 dark:hx-text-neutral-400 dark:hover:hx-bg-primary-100/5 dark:hover:hx-text-gray-50 contrast-more:dark:hx-text-gray-50 contrast-more:dark:hover:hx-border-gray-50"
    href="/docs/chapter18/"
    
  >第 18 章：表访问方法
    </a>
              
            </li><li class="hx-flex hx-flex-col open"><a
    class="hx-flex hx-items-center hx-justify-between hx-gap-2 hx-cursor-pointer hx-rounded hx-px-2 hx-py-1.5 hx-text-sm hx-transition-colors [-webkit-tap-highlight-color:transparent] [-webkit-touch-callout:none] [word-break:break-word]
      hx-text-gray-500 hover:hx-bg-gray-100 hover:hx-text-gray-900 contrast-more:hx-border contrast-more:hx-border-transparent contrast-more:hx-text-gray-900 contrast-more:hover:hx-border-gray-900 dark:hx-text-neutral-400 dark:hover:hx-bg-primary-100/5 dark:hover:hx-text-gray-50 contrast-more:dark:hx-text-gray-50 contrast-more:dark:hover:hx-border-gray-50"
    href="/docs/chapter19/"
    
  >第 19 章：索引访问方法
    </a>
              
            </li><li class="hx-flex hx-flex-col open"><a
    class="hx-flex hx-items-center hx-justify-between hx-gap-2 hx-cursor-pointer hx-rounded hx-px-2 hx-py-1.5 hx-text-sm hx-transition-colors [-webkit-tap-highlight-color:transparent] [-webkit-touch-callout:none] [word-break:break-word]
      hx-text-gray-500 hover:hx-bg-gray-100 hover:hx-text-gray-900 contrast-more:hx-border contrast-more:hx-border-transparent contrast-more:hx-text-gray-900 contrast-more:hover:hx-border-gray-900 dark:hx-text-neutral-400 dark:hover:hx-bg-primary-100/5 dark:hover:hx-text-gray-50 contrast-more:dark:hx-text-gray-50 contrast-more:dark:hover:hx-border-gray-50"
    href="/docs/chapter20/"
    
  >第 20 章：索引扫描
    </a>
              
            </li><li class="hx-flex hx-flex-col open"><a
    class="hx-flex hx-items-center hx-justify-between hx-gap-2 hx-cursor-pointer hx-rounded hx-px-2 hx-py-1.5 hx-text-sm hx-transition-colors [-webkit-tap-highlight-color:transparent] [-webkit-touch-callout:none] [word-break:break-word]
      hx-text-gray-500 hover:hx-bg-gray-100 hover:hx-text-gray-900 contrast-more:hx-border contrast-more:hx-border-transparent contrast-more:hx-text-gray-900 contrast-more:hover:hx-border-gray-900 dark:hx-text-neutral-400 dark:hover:hx-bg-primary-100/5 dark:hover:hx-text-gray-50 contrast-more:dark:hx-text-gray-50 contrast-more:dark:hover:hx-border-gray-50"
    href="/docs/chapter21/"
    
  >第 21 章：嵌套循环
    </a>
              
            </li><li class="hx-flex hx-flex-col open"><a
    class="hx-flex hx-items-center hx-justify-between hx-gap-2 hx-cursor-pointer hx-rounded hx-px-2 hx-py-1.5 hx-text-sm hx-transition-colors [-webkit-tap-highlight-color:transparent] [-webkit-touch-callout:none] [word-break:break-word]
      sidebar-active-item hx-bg-primary-100 hx-font-semibold hx-text-primary-800 contrast-more:hx-border contrast-more:hx-border-primary-500 dark:hx-bg-primary-400/10 dark:hx-text-primary-600 contrast-more:dark:hx-border-primary-500"
    href="/docs/chapter22/"
    
  >第 22 章：哈希
    </a>
  
    <ul class='hx-flex hx-flex-col hx-gap-1 hx-relative before:hx-absolute before:hx-inset-y-1 before:hx-w-px before:hx-bg-gray-200 before:hx-content-[""] dark:before:hx-bg-neutral-800 ltr:hx-pl-3 ltr:before:hx-left-0 rtl:hx-pr-3 rtl:before:hx-right-0 ltr:hx-ml-3 rtl:hx-mr-3'><li>
              <a
                href="#221-%e5%93%88%e5%b8%8c%e8%bf%9e%e6%8e%a5"
                class="hx-flex hx-rounded hx-px-2 hx-py-1.5 hx-text-sm hx-transition-colors [word-break:break-word] hx-cursor-pointer [-webkit-tap-highlight-color:transparent] [-webkit-touch-callout:none] contrast-more:hx-border hx-gap-2 before:hx-opacity-25 before:hx-content-['#'] hx-text-gray-500 hover:hx-bg-gray-100 hover:hx-text-gray-900 dark:hx-text-neutral-400 dark:hover:hx-bg-primary-100/5 dark:hover:hx-text-gray-50 contrast-more:hx-text-gray-900 contrast-more:dark:hx-text-gray-50 contrast-more:hx-border-transparent contrast-more:hover:hx-border-gray-900 contrast-more:dark:hover:hx-border-gray-50"
              >22.1 哈希连接</a>
            </li>
          <li>
              <a
                href="#222-%e9%9d%9e%e9%87%8d%e5%a4%8d%e5%80%bc%e4%b8%8e%e5%88%86%e7%bb%84"
                class="hx-flex hx-rounded hx-px-2 hx-py-1.5 hx-text-sm hx-transition-colors [word-break:break-word] hx-cursor-pointer [-webkit-tap-highlight-color:transparent] [-webkit-touch-callout:none] contrast-more:hx-border hx-gap-2 before:hx-opacity-25 before:hx-content-['#'] hx-text-gray-500 hover:hx-bg-gray-100 hover:hx-text-gray-900 dark:hx-text-neutral-400 dark:hover:hx-bg-primary-100/5 dark:hover:hx-text-gray-50 contrast-more:hx-text-gray-900 contrast-more:dark:hx-text-gray-50 contrast-more:hx-border-transparent contrast-more:hover:hx-border-gray-900 contrast-more:dark:hover:hx-border-gray-50"
              >22.2 非重复值与分组</a>
            </li>
          </ul>
  
              
            </li><li class="hx-flex hx-flex-col open"><a
    class="hx-flex hx-items-center hx-justify-between hx-gap-2 hx-cursor-pointer hx-rounded hx-px-2 hx-py-1.5 hx-text-sm hx-transition-colors [-webkit-tap-highlight-color:transparent] [-webkit-touch-callout:none] [word-break:break-word]
      hx-text-gray-500 hover:hx-bg-gray-100 hover:hx-text-gray-900 contrast-more:hx-border contrast-more:hx-border-transparent contrast-more:hx-text-gray-900 contrast-more:hover:hx-border-gray-900 dark:hx-text-neutral-400 dark:hover:hx-bg-primary-100/5 dark:hover:hx-text-gray-50 contrast-more:dark:hx-text-gray-50 contrast-more:dark:hover:hx-border-gray-50"
    href="/docs/chapter23/"
    
  >第 23 章：排序与归并
    </a>
              
            </li><li class="hx-flex hx-flex-col open"><a
    class="hx-flex hx-items-center hx-justify-between hx-gap-2 hx-cursor-pointer hx-rounded hx-px-2 hx-py-1.5 hx-text-sm hx-transition-colors [-webkit-tap-highlight-color:transparent] [-webkit-touch-callout:none] [word-break:break-word]
      hx-text-gray-500 hover:hx-bg-gray-100 hover:hx-text-gray-900 contrast-more:hx-border contrast-more:hx-border-transparent contrast-more:hx-text-gray-900 contrast-more:hover:hx-border-gray-900 dark:hx-text-neutral-400 dark:hover:hx-bg-primary-100/5 dark:hover:hx-text-gray-50 contrast-more:dark:hx-text-gray-50 contrast-more:dark:hover:hx-border-gray-50"
    href="/docs/chapter24/"
    
  >第 24 章：Hash
    </a>
              
            </li><li class="hx-flex hx-flex-col open"><a
    class="hx-flex hx-items-center hx-justify-between hx-gap-2 hx-cursor-pointer hx-rounded hx-px-2 hx-py-1.5 hx-text-sm hx-transition-colors [-webkit-tap-highlight-color:transparent] [-webkit-touch-callout:none] [word-break:break-word]
      hx-text-gray-500 hover:hx-bg-gray-100 hover:hx-text-gray-900 contrast-more:hx-border contrast-more:hx-border-transparent contrast-more:hx-text-gray-900 contrast-more:hover:hx-border-gray-900 dark:hx-text-neutral-400 dark:hover:hx-bg-primary-100/5 dark:hover:hx-text-gray-50 contrast-more:dark:hx-text-gray-50 contrast-more:dark:hover:hx-border-gray-50"
    href="/docs/chapter25/"
    
  >第 25 章：B-tree
    </a>
              
            </li><li class="hx-flex hx-flex-col open"><a
    class="hx-flex hx-items-center hx-justify-between hx-gap-2 hx-cursor-pointer hx-rounded hx-px-2 hx-py-1.5 hx-text-sm hx-transition-colors [-webkit-tap-highlight-color:transparent] [-webkit-touch-callout:none] [word-break:break-word]
      hx-text-gray-500 hover:hx-bg-gray-100 hover:hx-text-gray-900 contrast-more:hx-border contrast-more:hx-border-transparent contrast-more:hx-text-gray-900 contrast-more:hover:hx-border-gray-900 dark:hx-text-neutral-400 dark:hover:hx-bg-primary-100/5 dark:hover:hx-text-gray-50 contrast-more:dark:hx-text-gray-50 contrast-more:dark:hover:hx-border-gray-50"
    href="/docs/chapter26/"
    
  >第 26 章：GiST
    </a>
              
            </li><li class="hx-flex hx-flex-col open"><a
    class="hx-flex hx-items-center hx-justify-between hx-gap-2 hx-cursor-pointer hx-rounded hx-px-2 hx-py-1.5 hx-text-sm hx-transition-colors [-webkit-tap-highlight-color:transparent] [-webkit-touch-callout:none] [word-break:break-word]
      hx-text-gray-500 hover:hx-bg-gray-100 hover:hx-text-gray-900 contrast-more:hx-border contrast-more:hx-border-transparent contrast-more:hx-text-gray-900 contrast-more:hover:hx-border-gray-900 dark:hx-text-neutral-400 dark:hover:hx-bg-primary-100/5 dark:hover:hx-text-gray-50 contrast-more:dark:hx-text-gray-50 contrast-more:dark:hover:hx-border-gray-50"
    href="/docs/chapter27/"
    
  >第 27 章：SP-GiST
    </a>
              
            </li><li class="hx-flex hx-flex-col open"><a
    class="hx-flex hx-items-center hx-justify-between hx-gap-2 hx-cursor-pointer hx-rounded hx-px-2 hx-py-1.5 hx-text-sm hx-transition-colors [-webkit-tap-highlight-color:transparent] [-webkit-touch-callout:none] [word-break:break-word]
      hx-text-gray-500 hover:hx-bg-gray-100 hover:hx-text-gray-900 contrast-more:hx-border contrast-more:hx-border-transparent contrast-more:hx-text-gray-900 contrast-more:hover:hx-border-gray-900 dark:hx-text-neutral-400 dark:hover:hx-bg-primary-100/5 dark:hover:hx-text-gray-50 contrast-more:dark:hx-text-gray-50 contrast-more:dark:hover:hx-border-gray-50"
    href="/docs/chapter28/"
    
  >第 28 章：GIN
    </a>
              
            </li><li class="hx-flex hx-flex-col open"><a
    class="hx-flex hx-items-center hx-justify-between hx-gap-2 hx-cursor-pointer hx-rounded hx-px-2 hx-py-1.5 hx-text-sm hx-transition-colors [-webkit-tap-highlight-color:transparent] [-webkit-touch-callout:none] [word-break:break-word]
      hx-text-gray-500 hover:hx-bg-gray-100 hover:hx-text-gray-900 contrast-more:hx-border contrast-more:hx-border-transparent contrast-more:hx-text-gray-900 contrast-more:hover:hx-border-gray-900 dark:hx-text-neutral-400 dark:hover:hx-bg-primary-100/5 dark:hover:hx-text-gray-50 contrast-more:dark:hx-text-gray-50 contrast-more:dark:hover:hx-border-gray-50"
    href="/docs/chapter29/"
    
  >第 29 章：BRIN
    </a>
              
            </li><li class="hx-flex hx-flex-col open"><a
    class="hx-flex hx-items-center hx-justify-between hx-gap-2 hx-cursor-pointer hx-rounded hx-px-2 hx-py-1.5 hx-text-sm hx-transition-colors [-webkit-tap-highlight-color:transparent] [-webkit-touch-callout:none] [word-break:break-word]
      hx-text-gray-500 hover:hx-bg-gray-100 hover:hx-text-gray-900 contrast-more:hx-border contrast-more:hx-border-transparent contrast-more:hx-text-gray-900 contrast-more:hover:hx-border-gray-900 dark:hx-text-neutral-400 dark:hover:hx-bg-primary-100/5 dark:hover:hx-text-gray-50 contrast-more:dark:hx-text-gray-50 contrast-more:dark:hover:hx-border-gray-50"
    href="/docs/chapter30/"
    
  >结论
    </a>
              
            </li></ul>
      </div></li>
          <li class=""><a
    class="hx-flex hx-items-center hx-justify-between hx-gap-2 hx-cursor-pointer hx-rounded hx-px-2 hx-py-1.5 hx-text-sm hx-transition-colors [-webkit-tap-highlight-color:transparent] [-webkit-touch-callout:none] [word-break:break-word]
      hx-text-gray-500 hover:hx-bg-gray-100 hover:hx-text-gray-900 contrast-more:hx-border contrast-more:hx-border-transparent contrast-more:hx-text-gray-900 contrast-more:hover:hx-border-gray-900 dark:hx-text-neutral-400 dark:hover:hx-bg-primary-100/5 dark:hover:hx-text-gray-50 contrast-more:dark:hx-text-gray-50 contrast-more:dark:hover:hx-border-gray-50"
    href="/recommend/"
    
  >Recommend
    </a></li>
    </ul>

    <ul class="hx-flex hx-flex-col hx-gap-1 max-md:hx-hidden">
        
          <li class="open"><a
    class="hx-flex hx-items-center hx-justify-between hx-gap-2 hx-cursor-pointer hx-rounded hx-px-2 hx-py-1.5 hx-text-sm hx-transition-colors [-webkit-tap-highlight-color:transparent] [-webkit-touch-callout:none] [word-break:break-word]
      hx-text-gray-500 hover:hx-bg-gray-100 hover:hx-text-gray-900 contrast-more:hx-border contrast-more:hx-border-transparent contrast-more:hx-text-gray-900 contrast-more:hover:hx-border-gray-900 dark:hx-text-neutral-400 dark:hover:hx-bg-primary-100/5 dark:hover:hx-text-gray-50 contrast-more:dark:hx-text-gray-50 contrast-more:dark:hover:hx-border-gray-50"
    href="/docs/chapter01/"
    
  >第 1 章：介绍
    </a></li>
          <li class="open"><a
    class="hx-flex hx-items-center hx-justify-between hx-gap-2 hx-cursor-pointer hx-rounded hx-px-2 hx-py-1.5 hx-text-sm hx-transition-colors [-webkit-tap-highlight-color:transparent] [-webkit-touch-callout:none] [word-break:break-word]
      hx-text-gray-500 hover:hx-bg-gray-100 hover:hx-text-gray-900 contrast-more:hx-border contrast-more:hx-border-transparent contrast-more:hx-text-gray-900 contrast-more:hover:hx-border-gray-900 dark:hx-text-neutral-400 dark:hover:hx-bg-primary-100/5 dark:hover:hx-text-gray-50 contrast-more:dark:hx-text-gray-50 contrast-more:dark:hover:hx-border-gray-50"
    href="/docs/chapter02/"
    
  >第 2 章：隔离性
    </a></li>
          <li class="open"><a
    class="hx-flex hx-items-center hx-justify-between hx-gap-2 hx-cursor-pointer hx-rounded hx-px-2 hx-py-1.5 hx-text-sm hx-transition-colors [-webkit-tap-highlight-color:transparent] [-webkit-touch-callout:none] [word-break:break-word]
      hx-text-gray-500 hover:hx-bg-gray-100 hover:hx-text-gray-900 contrast-more:hx-border contrast-more:hx-border-transparent contrast-more:hx-text-gray-900 contrast-more:hover:hx-border-gray-900 dark:hx-text-neutral-400 dark:hover:hx-bg-primary-100/5 dark:hover:hx-text-gray-50 contrast-more:dark:hx-text-gray-50 contrast-more:dark:hover:hx-border-gray-50"
    href="/docs/chapter03/"
    
  >第 3 章：页与元组
    </a></li>
          <li class="open"><a
    class="hx-flex hx-items-center hx-justify-between hx-gap-2 hx-cursor-pointer hx-rounded hx-px-2 hx-py-1.5 hx-text-sm hx-transition-colors [-webkit-tap-highlight-color:transparent] [-webkit-touch-callout:none] [word-break:break-word]
      hx-text-gray-500 hover:hx-bg-gray-100 hover:hx-text-gray-900 contrast-more:hx-border contrast-more:hx-border-transparent contrast-more:hx-text-gray-900 contrast-more:hover:hx-border-gray-900 dark:hx-text-neutral-400 dark:hover:hx-bg-primary-100/5 dark:hover:hx-text-gray-50 contrast-more:dark:hx-text-gray-50 contrast-more:dark:hover:hx-border-gray-50"
    href="/docs/chapter04/"
    
  >第 4 章：快照
    </a></li>
          <li class="open"><a
    class="hx-flex hx-items-center hx-justify-between hx-gap-2 hx-cursor-pointer hx-rounded hx-px-2 hx-py-1.5 hx-text-sm hx-transition-colors [-webkit-tap-highlight-color:transparent] [-webkit-touch-callout:none] [word-break:break-word]
      hx-text-gray-500 hover:hx-bg-gray-100 hover:hx-text-gray-900 contrast-more:hx-border contrast-more:hx-border-transparent contrast-more:hx-text-gray-900 contrast-more:hover:hx-border-gray-900 dark:hx-text-neutral-400 dark:hover:hx-bg-primary-100/5 dark:hover:hx-text-gray-50 contrast-more:dark:hx-text-gray-50 contrast-more:dark:hover:hx-border-gray-50"
    href="/docs/chapter05/"
    
  >第 5 章：页剪枝与 HOT 更新
    </a></li>
          <li class="open"><a
    class="hx-flex hx-items-center hx-justify-between hx-gap-2 hx-cursor-pointer hx-rounded hx-px-2 hx-py-1.5 hx-text-sm hx-transition-colors [-webkit-tap-highlight-color:transparent] [-webkit-touch-callout:none] [word-break:break-word]
      hx-text-gray-500 hover:hx-bg-gray-100 hover:hx-text-gray-900 contrast-more:hx-border contrast-more:hx-border-transparent contrast-more:hx-text-gray-900 contrast-more:hover:hx-border-gray-900 dark:hx-text-neutral-400 dark:hover:hx-bg-primary-100/5 dark:hover:hx-text-gray-50 contrast-more:dark:hx-text-gray-50 contrast-more:dark:hover:hx-border-gray-50"
    href="/docs/chapter06/"
    
  >第 6 章：Vacuum 与 Autovacuum
    </a></li>
          <li class="open"><a
    class="hx-flex hx-items-center hx-justify-between hx-gap-2 hx-cursor-pointer hx-rounded hx-px-2 hx-py-1.5 hx-text-sm hx-transition-colors [-webkit-tap-highlight-color:transparent] [-webkit-touch-callout:none] [word-break:break-word]
      hx-text-gray-500 hover:hx-bg-gray-100 hover:hx-text-gray-900 contrast-more:hx-border contrast-more:hx-border-transparent contrast-more:hx-text-gray-900 contrast-more:hover:hx-border-gray-900 dark:hx-text-neutral-400 dark:hover:hx-bg-primary-100/5 dark:hover:hx-text-gray-50 contrast-more:dark:hx-text-gray-50 contrast-more:dark:hover:hx-border-gray-50"
    href="/docs/chapter07/"
    
  >第 7 章：冻结
    </a></li>
          <li class="open"><a
    class="hx-flex hx-items-center hx-justify-between hx-gap-2 hx-cursor-pointer hx-rounded hx-px-2 hx-py-1.5 hx-text-sm hx-transition-colors [-webkit-tap-highlight-color:transparent] [-webkit-touch-callout:none] [word-break:break-word]
      hx-text-gray-500 hover:hx-bg-gray-100 hover:hx-text-gray-900 contrast-more:hx-border contrast-more:hx-border-transparent contrast-more:hx-text-gray-900 contrast-more:hover:hx-border-gray-900 dark:hx-text-neutral-400 dark:hover:hx-bg-primary-100/5 dark:hover:hx-text-gray-50 contrast-more:dark:hx-text-gray-50 contrast-more:dark:hover:hx-border-gray-50"
    href="/docs/chapter08/"
    
  >第 8 章：重建表与索引
    </a></li>
          <li class="open"><a
    class="hx-flex hx-items-center hx-justify-between hx-gap-2 hx-cursor-pointer hx-rounded hx-px-2 hx-py-1.5 hx-text-sm hx-transition-colors [-webkit-tap-highlight-color:transparent] [-webkit-touch-callout:none] [word-break:break-word]
      hx-text-gray-500 hover:hx-bg-gray-100 hover:hx-text-gray-900 contrast-more:hx-border contrast-more:hx-border-transparent contrast-more:hx-text-gray-900 contrast-more:hover:hx-border-gray-900 dark:hx-text-neutral-400 dark:hover:hx-bg-primary-100/5 dark:hover:hx-text-gray-50 contrast-more:dark:hx-text-gray-50 contrast-more:dark:hover:hx-border-gray-50"
    href="/docs/chapter09/"
    
  >第 9 章：缓冲区缓存
    </a></li>
          <li class="open"><a
    class="hx-flex hx-items-center hx-justify-between hx-gap-2 hx-cursor-pointer hx-rounded hx-px-2 hx-py-1.5 hx-text-sm hx-transition-colors [-webkit-tap-highlight-color:transparent] [-webkit-touch-callout:none] [word-break:break-word]
      hx-text-gray-500 hover:hx-bg-gray-100 hover:hx-text-gray-900 contrast-more:hx-border contrast-more:hx-border-transparent contrast-more:hx-text-gray-900 contrast-more:hover:hx-border-gray-900 dark:hx-text-neutral-400 dark:hover:hx-bg-primary-100/5 dark:hover:hx-text-gray-50 contrast-more:dark:hx-text-gray-50 contrast-more:dark:hover:hx-border-gray-50"
    href="/docs/chapter10/"
    
  >第 10 章：预写式日志
    </a></li>
          <li class="open"><a
    class="hx-flex hx-items-center hx-justify-between hx-gap-2 hx-cursor-pointer hx-rounded hx-px-2 hx-py-1.5 hx-text-sm hx-transition-colors [-webkit-tap-highlight-color:transparent] [-webkit-touch-callout:none] [word-break:break-word]
      hx-text-gray-500 hover:hx-bg-gray-100 hover:hx-text-gray-900 contrast-more:hx-border contrast-more:hx-border-transparent contrast-more:hx-text-gray-900 contrast-more:hover:hx-border-gray-900 dark:hx-text-neutral-400 dark:hover:hx-bg-primary-100/5 dark:hover:hx-text-gray-50 contrast-more:dark:hx-text-gray-50 contrast-more:dark:hover:hx-border-gray-50"
    href="/docs/chapter11/"
    
  >第 11 章：WAL 模式
    </a></li>
          <li class="open"><a
    class="hx-flex hx-items-center hx-justify-between hx-gap-2 hx-cursor-pointer hx-rounded hx-px-2 hx-py-1.5 hx-text-sm hx-transition-colors [-webkit-tap-highlight-color:transparent] [-webkit-touch-callout:none] [word-break:break-word]
      hx-text-gray-500 hover:hx-bg-gray-100 hover:hx-text-gray-900 contrast-more:hx-border contrast-more:hx-border-transparent contrast-more:hx-text-gray-900 contrast-more:hover:hx-border-gray-900 dark:hx-text-neutral-400 dark:hover:hx-bg-primary-100/5 dark:hover:hx-text-gray-50 contrast-more:dark:hx-text-gray-50 contrast-more:dark:hover:hx-border-gray-50"
    href="/docs/chapter12/"
    
  >第 12 章：关系级锁
    </a></li>
          <li class="open"><a
    class="hx-flex hx-items-center hx-justify-between hx-gap-2 hx-cursor-pointer hx-rounded hx-px-2 hx-py-1.5 hx-text-sm hx-transition-colors [-webkit-tap-highlight-color:transparent] [-webkit-touch-callout:none] [word-break:break-word]
      hx-text-gray-500 hover:hx-bg-gray-100 hover:hx-text-gray-900 contrast-more:hx-border contrast-more:hx-border-transparent contrast-more:hx-text-gray-900 contrast-more:hover:hx-border-gray-900 dark:hx-text-neutral-400 dark:hover:hx-bg-primary-100/5 dark:hover:hx-text-gray-50 contrast-more:dark:hx-text-gray-50 contrast-more:dark:hover:hx-border-gray-50"
    href="/docs/chapter13/"
    
  >第 13 章：行级锁
    </a></li>
          <li class="open"><a
    class="hx-flex hx-items-center hx-justify-between hx-gap-2 hx-cursor-pointer hx-rounded hx-px-2 hx-py-1.5 hx-text-sm hx-transition-colors [-webkit-tap-highlight-color:transparent] [-webkit-touch-callout:none] [word-break:break-word]
      hx-text-gray-500 hover:hx-bg-gray-100 hover:hx-text-gray-900 contrast-more:hx-border contrast-more:hx-border-transparent contrast-more:hx-text-gray-900 contrast-more:hover:hx-border-gray-900 dark:hx-text-neutral-400 dark:hover:hx-bg-primary-100/5 dark:hover:hx-text-gray-50 contrast-more:dark:hx-text-gray-50 contrast-more:dark:hover:hx-border-gray-50"
    href="/docs/chapter14/"
    
  >第 14 章：多样的锁
    </a></li>
          <li class="open"><a
    class="hx-flex hx-items-center hx-justify-between hx-gap-2 hx-cursor-pointer hx-rounded hx-px-2 hx-py-1.5 hx-text-sm hx-transition-colors [-webkit-tap-highlight-color:transparent] [-webkit-touch-callout:none] [word-break:break-word]
      hx-text-gray-500 hover:hx-bg-gray-100 hover:hx-text-gray-900 contrast-more:hx-border contrast-more:hx-border-transparent contrast-more:hx-text-gray-900 contrast-more:hover:hx-border-gray-900 dark:hx-text-neutral-400 dark:hover:hx-bg-primary-100/5 dark:hover:hx-text-gray-50 contrast-more:dark:hx-text-gray-50 contrast-more:dark:hover:hx-border-gray-50"
    href="/docs/chapter15/"
    
  >第 15 章：内存结构上的锁
    </a></li>
          <li class="open"><a
    class="hx-flex hx-items-center hx-justify-between hx-gap-2 hx-cursor-pointer hx-rounded hx-px-2 hx-py-1.5 hx-text-sm hx-transition-colors [-webkit-tap-highlight-color:transparent] [-webkit-touch-callout:none] [word-break:break-word]
      hx-text-gray-500 hover:hx-bg-gray-100 hover:hx-text-gray-900 contrast-more:hx-border contrast-more:hx-border-transparent contrast-more:hx-text-gray-900 contrast-more:hover:hx-border-gray-900 dark:hx-text-neutral-400 dark:hover:hx-bg-primary-100/5 dark:hover:hx-text-gray-50 contrast-more:dark:hx-text-gray-50 contrast-more:dark:hover:hx-border-gray-50"
    href="/docs/chapter16/"
    
  >第 16 章：查询执行阶段
    </a></li>
          <li class="open"><a
    class="hx-flex hx-items-center hx-justify-between hx-gap-2 hx-cursor-pointer hx-rounded hx-px-2 hx-py-1.5 hx-text-sm hx-transition-colors [-webkit-tap-highlight-color:transparent] [-webkit-touch-callout:none] [word-break:break-word]
      hx-text-gray-500 hover:hx-bg-gray-100 hover:hx-text-gray-900 contrast-more:hx-border contrast-more:hx-border-transparent contrast-more:hx-text-gray-900 contrast-more:hover:hx-border-gray-900 dark:hx-text-neutral-400 dark:hover:hx-bg-primary-100/5 dark:hover:hx-text-gray-50 contrast-more:dark:hx-text-gray-50 contrast-more:dark:hover:hx-border-gray-50"
    href="/docs/chapter17/"
    
  >第 17 章：统计信息
    </a></li>
          <li class="open"><a
    class="hx-flex hx-items-center hx-justify-between hx-gap-2 hx-cursor-pointer hx-rounded hx-px-2 hx-py-1.5 hx-text-sm hx-transition-colors [-webkit-tap-highlight-color:transparent] [-webkit-touch-callout:none] [word-break:break-word]
      hx-text-gray-500 hover:hx-bg-gray-100 hover:hx-text-gray-900 contrast-more:hx-border contrast-more:hx-border-transparent contrast-more:hx-text-gray-900 contrast-more:hover:hx-border-gray-900 dark:hx-text-neutral-400 dark:hover:hx-bg-primary-100/5 dark:hover:hx-text-gray-50 contrast-more:dark:hx-text-gray-50 contrast-more:dark:hover:hx-border-gray-50"
    href="/docs/chapter18/"
    
  >第 18 章：表访问方法
    </a></li>
          <li class="open"><a
    class="hx-flex hx-items-center hx-justify-between hx-gap-2 hx-cursor-pointer hx-rounded hx-px-2 hx-py-1.5 hx-text-sm hx-transition-colors [-webkit-tap-highlight-color:transparent] [-webkit-touch-callout:none] [word-break:break-word]
      hx-text-gray-500 hover:hx-bg-gray-100 hover:hx-text-gray-900 contrast-more:hx-border contrast-more:hx-border-transparent contrast-more:hx-text-gray-900 contrast-more:hover:hx-border-gray-900 dark:hx-text-neutral-400 dark:hover:hx-bg-primary-100/5 dark:hover:hx-text-gray-50 contrast-more:dark:hx-text-gray-50 contrast-more:dark:hover:hx-border-gray-50"
    href="/docs/chapter19/"
    
  >第 19 章：索引访问方法
    </a></li>
          <li class="open"><a
    class="hx-flex hx-items-center hx-justify-between hx-gap-2 hx-cursor-pointer hx-rounded hx-px-2 hx-py-1.5 hx-text-sm hx-transition-colors [-webkit-tap-highlight-color:transparent] [-webkit-touch-callout:none] [word-break:break-word]
      hx-text-gray-500 hover:hx-bg-gray-100 hover:hx-text-gray-900 contrast-more:hx-border contrast-more:hx-border-transparent contrast-more:hx-text-gray-900 contrast-more:hover:hx-border-gray-900 dark:hx-text-neutral-400 dark:hover:hx-bg-primary-100/5 dark:hover:hx-text-gray-50 contrast-more:dark:hx-text-gray-50 contrast-more:dark:hover:hx-border-gray-50"
    href="/docs/chapter20/"
    
  >第 20 章：索引扫描
    </a></li>
          <li class="open"><a
    class="hx-flex hx-items-center hx-justify-between hx-gap-2 hx-cursor-pointer hx-rounded hx-px-2 hx-py-1.5 hx-text-sm hx-transition-colors [-webkit-tap-highlight-color:transparent] [-webkit-touch-callout:none] [word-break:break-word]
      hx-text-gray-500 hover:hx-bg-gray-100 hover:hx-text-gray-900 contrast-more:hx-border contrast-more:hx-border-transparent contrast-more:hx-text-gray-900 contrast-more:hover:hx-border-gray-900 dark:hx-text-neutral-400 dark:hover:hx-bg-primary-100/5 dark:hover:hx-text-gray-50 contrast-more:dark:hx-text-gray-50 contrast-more:dark:hover:hx-border-gray-50"
    href="/docs/chapter21/"
    
  >第 21 章：嵌套循环
    </a></li>
          <li class="open"><a
    class="hx-flex hx-items-center hx-justify-between hx-gap-2 hx-cursor-pointer hx-rounded hx-px-2 hx-py-1.5 hx-text-sm hx-transition-colors [-webkit-tap-highlight-color:transparent] [-webkit-touch-callout:none] [word-break:break-word]
      sidebar-active-item hx-bg-primary-100 hx-font-semibold hx-text-primary-800 contrast-more:hx-border contrast-more:hx-border-primary-500 dark:hx-bg-primary-400/10 dark:hx-text-primary-600 contrast-more:dark:hx-border-primary-500"
    href="/docs/chapter22/"
    
  >第 22 章：哈希
    </a></li>
          <li class="open"><a
    class="hx-flex hx-items-center hx-justify-between hx-gap-2 hx-cursor-pointer hx-rounded hx-px-2 hx-py-1.5 hx-text-sm hx-transition-colors [-webkit-tap-highlight-color:transparent] [-webkit-touch-callout:none] [word-break:break-word]
      hx-text-gray-500 hover:hx-bg-gray-100 hover:hx-text-gray-900 contrast-more:hx-border contrast-more:hx-border-transparent contrast-more:hx-text-gray-900 contrast-more:hover:hx-border-gray-900 dark:hx-text-neutral-400 dark:hover:hx-bg-primary-100/5 dark:hover:hx-text-gray-50 contrast-more:dark:hx-text-gray-50 contrast-more:dark:hover:hx-border-gray-50"
    href="/docs/chapter23/"
    
  >第 23 章：排序与归并
    </a></li>
          <li class="open"><a
    class="hx-flex hx-items-center hx-justify-between hx-gap-2 hx-cursor-pointer hx-rounded hx-px-2 hx-py-1.5 hx-text-sm hx-transition-colors [-webkit-tap-highlight-color:transparent] [-webkit-touch-callout:none] [word-break:break-word]
      hx-text-gray-500 hover:hx-bg-gray-100 hover:hx-text-gray-900 contrast-more:hx-border contrast-more:hx-border-transparent contrast-more:hx-text-gray-900 contrast-more:hover:hx-border-gray-900 dark:hx-text-neutral-400 dark:hover:hx-bg-primary-100/5 dark:hover:hx-text-gray-50 contrast-more:dark:hx-text-gray-50 contrast-more:dark:hover:hx-border-gray-50"
    href="/docs/chapter24/"
    
  >第 24 章：Hash
    </a></li>
          <li class="open"><a
    class="hx-flex hx-items-center hx-justify-between hx-gap-2 hx-cursor-pointer hx-rounded hx-px-2 hx-py-1.5 hx-text-sm hx-transition-colors [-webkit-tap-highlight-color:transparent] [-webkit-touch-callout:none] [word-break:break-word]
      hx-text-gray-500 hover:hx-bg-gray-100 hover:hx-text-gray-900 contrast-more:hx-border contrast-more:hx-border-transparent contrast-more:hx-text-gray-900 contrast-more:hover:hx-border-gray-900 dark:hx-text-neutral-400 dark:hover:hx-bg-primary-100/5 dark:hover:hx-text-gray-50 contrast-more:dark:hx-text-gray-50 contrast-more:dark:hover:hx-border-gray-50"
    href="/docs/chapter25/"
    
  >第 25 章：B-tree
    </a></li>
          <li class="open"><a
    class="hx-flex hx-items-center hx-justify-between hx-gap-2 hx-cursor-pointer hx-rounded hx-px-2 hx-py-1.5 hx-text-sm hx-transition-colors [-webkit-tap-highlight-color:transparent] [-webkit-touch-callout:none] [word-break:break-word]
      hx-text-gray-500 hover:hx-bg-gray-100 hover:hx-text-gray-900 contrast-more:hx-border contrast-more:hx-border-transparent contrast-more:hx-text-gray-900 contrast-more:hover:hx-border-gray-900 dark:hx-text-neutral-400 dark:hover:hx-bg-primary-100/5 dark:hover:hx-text-gray-50 contrast-more:dark:hx-text-gray-50 contrast-more:dark:hover:hx-border-gray-50"
    href="/docs/chapter26/"
    
  >第 26 章：GiST
    </a></li>
          <li class="open"><a
    class="hx-flex hx-items-center hx-justify-between hx-gap-2 hx-cursor-pointer hx-rounded hx-px-2 hx-py-1.5 hx-text-sm hx-transition-colors [-webkit-tap-highlight-color:transparent] [-webkit-touch-callout:none] [word-break:break-word]
      hx-text-gray-500 hover:hx-bg-gray-100 hover:hx-text-gray-900 contrast-more:hx-border contrast-more:hx-border-transparent contrast-more:hx-text-gray-900 contrast-more:hover:hx-border-gray-900 dark:hx-text-neutral-400 dark:hover:hx-bg-primary-100/5 dark:hover:hx-text-gray-50 contrast-more:dark:hx-text-gray-50 contrast-more:dark:hover:hx-border-gray-50"
    href="/docs/chapter27/"
    
  >第 27 章：SP-GiST
    </a></li>
          <li class="open"><a
    class="hx-flex hx-items-center hx-justify-between hx-gap-2 hx-cursor-pointer hx-rounded hx-px-2 hx-py-1.5 hx-text-sm hx-transition-colors [-webkit-tap-highlight-color:transparent] [-webkit-touch-callout:none] [word-break:break-word]
      hx-text-gray-500 hover:hx-bg-gray-100 hover:hx-text-gray-900 contrast-more:hx-border contrast-more:hx-border-transparent contrast-more:hx-text-gray-900 contrast-more:hover:hx-border-gray-900 dark:hx-text-neutral-400 dark:hover:hx-bg-primary-100/5 dark:hover:hx-text-gray-50 contrast-more:dark:hx-text-gray-50 contrast-more:dark:hover:hx-border-gray-50"
    href="/docs/chapter28/"
    
  >第 28 章：GIN
    </a></li>
          <li class="open"><a
    class="hx-flex hx-items-center hx-justify-between hx-gap-2 hx-cursor-pointer hx-rounded hx-px-2 hx-py-1.5 hx-text-sm hx-transition-colors [-webkit-tap-highlight-color:transparent] [-webkit-touch-callout:none] [word-break:break-word]
      hx-text-gray-500 hover:hx-bg-gray-100 hover:hx-text-gray-900 contrast-more:hx-border contrast-more:hx-border-transparent contrast-more:hx-text-gray-900 contrast-more:hover:hx-border-gray-900 dark:hx-text-neutral-400 dark:hover:hx-bg-primary-100/5 dark:hover:hx-text-gray-50 contrast-more:dark:hx-text-gray-50 contrast-more:dark:hover:hx-border-gray-50"
    href="/docs/chapter29/"
    
  >第 29 章：BRIN
    </a></li>
          <li class="open"><a
    class="hx-flex hx-items-center hx-justify-between hx-gap-2 hx-cursor-pointer hx-rounded hx-px-2 hx-py-1.5 hx-text-sm hx-transition-colors [-webkit-tap-highlight-color:transparent] [-webkit-touch-callout:none] [word-break:break-word]
      hx-text-gray-500 hover:hx-bg-gray-100 hover:hx-text-gray-900 contrast-more:hx-border contrast-more:hx-border-transparent contrast-more:hx-text-gray-900 contrast-more:hover:hx-border-gray-900 dark:hx-text-neutral-400 dark:hover:hx-bg-primary-100/5 dark:hover:hx-text-gray-50 contrast-more:dark:hx-text-gray-50 contrast-more:dark:hover:hx-border-gray-50"
    href="/docs/chapter30/"
    
  >结论
    </a></li>
        
      </ul>
    </div>
  
  
    <div class="  hx-sticky hx-bottom-0 hx-bg-white dark:hx-bg-dark hx-mx-4 hx-py-4 hx-shadow-[0_-12px_16px_#fff] hx-flex hx-items-center hx-gap-2 dark:hx-border-neutral-800 dark:hx-shadow-[0_-12px_16px_#111] contrast-more:hx-border-neutral-400 contrast-more:hx-shadow-none contrast-more:dark:hx-shadow-none hx-border-t" data-toggle-animation="show"><div class="hx-flex hx-grow hx-flex-col"><button
  title="Change theme"
  data-theme="light"
  class="theme-toggle hx-group hx-h-7 hx-rounded-md hx-px-2 hx-text-left hx-text-xs hx-font-medium hx-text-gray-600 hx-transition-colors dark:hx-text-gray-400 hover:hx-bg-gray-100 hover:hx-text-gray-900 dark:hover:hx-bg-primary-100/5 dark:hover:hx-text-gray-50"
  type="button"
  aria-label="Change theme"
>
  <div class="hx-flex hx-items-center hx-gap-2 hx-capitalize"><svg height=12 class="group-data-[theme=light]:hx-hidden" xmlns="http://www.w3.org/2000/svg" fill="none" viewBox="0 0 24 24" stroke-width="2" stroke="currentColor" aria-hidden="true"><path stroke-linecap="round" stroke-linejoin="round" d="M12 3v1m0 16v1m9-9h-1M4 12H3m15.364 6.364l-.707-.707M6.343 6.343l-.707-.707m12.728 0l-.707.707M6.343 17.657l-.707.707M16 12a4 4 0 11-8 0 4 4 0 018 0z"/></svg><span class="group-data-[theme=light]:hx-hidden">Light</span><svg height=12 class="group-data-[theme=dark]:hx-hidden" xmlns="http://www.w3.org/2000/svg" fill="none" viewBox="0 0 24 24" stroke-width="2" stroke="currentColor" aria-hidden="true"><path stroke-linecap="round" stroke-linejoin="round" d="M20.354 15.354A9 9 0 018.646 3.646 9.003 9.003 0 0012 21a9.003 9.003 0 008.354-5.646z"/></svg><span class="group-data-[theme=dark]:hx-hidden">Dark</span></div>
</button>
</div></div></aside>
    
<nav class="hextra-toc hx-order-last hx-hidden hx-w-64 hx-shrink-0 xl:hx-block print:hx-hidden hx-px-4" aria-label="table of contents">
    <div class="hextra-scrollbar hx-sticky hx-top-16 hx-overflow-y-auto hx-pr-4 hx-pt-6 hx-text-sm [hyphens:auto] hx-max-h-[calc(100vh-var(--navbar-height)-env(safe-area-inset-bottom))] ltr:hx--mr-4 rtl:hx--ml-4"><p class="hx-mb-4 hx-font-semibold hx-tracking-tight">On this page</p><ul>
      <li class="hx-my-2 hx-scroll-my-6 hx-scroll-py-6">
        <a class="hx-font-semibold hx-inline-block hx-text-gray-500 hover:hx-text-gray-900 dark:hx-text-gray-400 dark:hover:hx-text-gray-300 contrast-more:hx-text-gray-900 contrast-more:hx-underline contrast-more:dark:hx-text-gray-50 hx-w-full hx-break-words" href="#221-%e5%93%88%e5%b8%8c%e8%bf%9e%e6%8e%a5">22.1 哈希连接
        </a>
      </li>
      <li class="hx-my-2 hx-scroll-my-6 hx-scroll-py-6">
        <a class="ltr:hx-pl-4 rtl:hx-pr-4 hx-inline-block hx-text-gray-500 hover:hx-text-gray-900 dark:hx-text-gray-400 dark:hover:hx-text-gray-300 contrast-more:hx-text-gray-900 contrast-more:hx-underline contrast-more:dark:hx-text-gray-50 hx-w-full hx-break-words" href="#2211-%e4%b8%80%e9%98%b6%e6%ae%b5%e5%93%88%e5%b8%8c%e8%bf%9e%e6%8e%a5">22.1.1 一阶段哈希连接
        </a>
      </li>
      <li class="hx-my-2 hx-scroll-my-6 hx-scroll-py-6">
        <a class="ltr:hx-pl-4 rtl:hx-pr-4 hx-inline-block hx-text-gray-500 hover:hx-text-gray-900 dark:hx-text-gray-400 dark:hover:hx-text-gray-300 contrast-more:hx-text-gray-900 contrast-more:hx-underline contrast-more:dark:hx-text-gray-50 hx-w-full hx-break-words" href="#2212-%e4%b8%a4%e9%98%b6%e6%ae%b5%e5%93%88%e5%b8%8c%e8%bf%9e%e6%8e%a5">22.1.2 两阶段哈希连接
        </a>
      </li>
      <li class="hx-my-2 hx-scroll-my-6 hx-scroll-py-6">
        <a class="ltr:hx-pl-4 rtl:hx-pr-4 hx-inline-block hx-text-gray-500 hover:hx-text-gray-900 dark:hx-text-gray-400 dark:hover:hx-text-gray-300 contrast-more:hx-text-gray-900 contrast-more:hx-underline contrast-more:dark:hx-text-gray-50 hx-w-full hx-break-words" href="#2213-%e5%8a%a8%e6%80%81%e8%b0%83%e6%95%b4">22.1.3 动态调整
        </a>
      </li>
      <li class="hx-my-2 hx-scroll-my-6 hx-scroll-py-6">
        <a class="ltr:hx-pl-4 rtl:hx-pr-4 hx-inline-block hx-text-gray-500 hover:hx-text-gray-900 dark:hx-text-gray-400 dark:hover:hx-text-gray-300 contrast-more:hx-text-gray-900 contrast-more:hx-underline contrast-more:dark:hx-text-gray-50 hx-w-full hx-break-words" href="#2214-span-classmarginalia-data-notev-96%e5%9c%a8%e5%b9%b6%e8%a1%8c%e8%ae%a1%e5%88%92%e4%b8%ad%e4%bd%bf%e7%94%a8%e5%93%88%e5%b8%8c%e8%bf%9e%e6%8e%a5span">22.1.4 在并行计划中使用哈希连接
        </a>
      </li>
      <li class="hx-my-2 hx-scroll-my-6 hx-scroll-py-6">
        <a class="ltr:hx-pl-4 rtl:hx-pr-4 hx-inline-block hx-text-gray-500 hover:hx-text-gray-900 dark:hx-text-gray-400 dark:hover:hx-text-gray-300 contrast-more:hx-text-gray-900 contrast-more:hx-underline contrast-more:dark:hx-text-gray-50 hx-w-full hx-break-words" href="#2215-span-classmarginalia-data-notev-11%e5%b9%b6%e8%a1%8c%e4%b8%80%e9%98%b6%e6%ae%b5%e5%93%88%e5%b8%8c%e8%bf%9e%e6%8e%a5span">22.1.5 并行一阶段哈希连接
        </a>
      </li>
      <li class="hx-my-2 hx-scroll-my-6 hx-scroll-py-6">
        <a class="ltr:hx-pl-4 rtl:hx-pr-4 hx-inline-block hx-text-gray-500 hover:hx-text-gray-900 dark:hx-text-gray-400 dark:hover:hx-text-gray-300 contrast-more:hx-text-gray-900 contrast-more:hx-underline contrast-more:dark:hx-text-gray-50 hx-w-full hx-break-words" href="#2216-span-classmarginalia-data-notev-11%e5%b9%b6%e8%a1%8c%e4%b8%a4%e9%98%b6%e6%ae%b5%e5%93%88%e5%b8%8c%e8%bf%9e%e6%8e%a5span">22.1.6 并行两阶段哈希连接
        </a>
      </li>
      <li class="hx-my-2 hx-scroll-my-6 hx-scroll-py-6">
        <a class="ltr:hx-pl-4 rtl:hx-pr-4 hx-inline-block hx-text-gray-500 hover:hx-text-gray-900 dark:hx-text-gray-400 dark:hover:hx-text-gray-300 contrast-more:hx-text-gray-900 contrast-more:hx-underline contrast-more:dark:hx-text-gray-50 hx-w-full hx-break-words" href="#2217-%e8%b0%83%e6%95%b4">22.1.7 调整
        </a>
      </li>
      <li class="hx-my-2 hx-scroll-my-6 hx-scroll-py-6">
        <a class="hx-font-semibold hx-inline-block hx-text-gray-500 hover:hx-text-gray-900 dark:hx-text-gray-400 dark:hover:hx-text-gray-300 contrast-more:hx-text-gray-900 contrast-more:hx-underline contrast-more:dark:hx-text-gray-50 hx-w-full hx-break-words" href="#222-%e9%9d%9e%e9%87%8d%e5%a4%8d%e5%80%bc%e4%b8%8e%e5%88%86%e7%bb%84">22.2 非重复值与分组
        </a>
      </li></ul>
      <div class="hx-mt-8 hx-border-t hx-bg-white hx-pt-8 hx-shadow-[0_-12px_16px_white] dark:hx-bg-dark dark:hx-shadow-[0_-12px_16px_#111] hx-sticky hx-bottom-0 hx-flex hx-flex-col hx-items-start hx-gap-2 hx-pb-8 dark:hx-border-neutral-800 contrast-more:hx-border-t contrast-more:hx-border-neutral-400 contrast-more:hx-shadow-none contrast-more:dark:hx-border-neutral-400">
        <button aria-hidden="true" id="backToTop" onClick="scrollUp();" class="hx-transition-all hx-duration-75 hx-opacity-0 hx-text-xs hx-font-medium hx-text-gray-500 hover:hx-text-gray-900 dark:hx-text-gray-400 dark:hover:hx-text-gray-100 contrast-more:hx-text-gray-800 contrast-more:dark:hx-text-gray-50">
          <span>Scroll to top</span>
          <svg xmlns="http://www.w3.org/2000/svg" fill="none" viewBox="0 0 24 24" stroke-width="1.5" stroke="currentColor" class="hx-inline ltr:hx-ml-1 rtl:hx-mr-1 hx-h-3.5 hx-w-3.5 hx-border hx-rounded-full hx-border-gray-500 hover:hx-border-gray-900 dark:hx-border-gray-400 dark:hover:hx-border-gray-100 contrast-more:hx-border-gray-800 contrast-more:dark:hx-border-gray-50">
            <path stroke-linecap="round" stroke-linejoin="round" d="M4.5 15.75l7.5-7.5 7.5 7.5" />
          </svg>
        </button>
      </div>
    </div>
  </nav>


    <article class="hx-w-full hx-break-words hx-flex hx-min-h-[calc(100vh-var(--navbar-height))] hx-min-w-0 hx-justify-center hx-pb-8 hx-pr-[calc(env(safe-area-inset-right)-1.5rem)]">
      <main class="hx-w-full hx-min-w-0 hx-max-w-6xl hx-px-6 hx-pt-4 md:hx-px-12">
        
  <div class="hx-mt-1.5 hx-flex hx-items-center hx-gap-1 hx-overflow-hidden hx-text-sm hx-text-gray-500 dark:hx-text-gray-400 contrast-more:hx-text-current">
        <div class="hx-whitespace-nowrap hx-transition-colors hx-min-w-[24px] hx-overflow-hidden hx-text-ellipsis hover:hx-text-gray-900 dark:hover:hx-text-gray-100">
          <a href="/docs/">Docs</a>
        </div><svg class="hx-w-3.5 hx-shrink-0 rtl:-hx-rotate-180" xmlns="http://www.w3.org/2000/svg" fill="none" viewBox="0 0 24 24" stroke-width="2" stroke="currentColor" aria-hidden="true"><path stroke-linecap="round" stroke-linejoin="round" d="M9 5l7 7-7 7"/></svg><div class="hx-whitespace-nowrap hx-transition-colors hx-font-medium hx-text-gray-700 contrast-more:hx-font-bold contrast-more:hx-text-current dark:hx-text-gray-100 contrast-more:dark:hx-text-current">第 22 章：哈希</div>
  </div>

        <div class="content">
          <h1>第 22 章：哈希</h1>
          <h2>22.1 哈希连接<span class="hx-absolute -hx-mt-20" id="221-哈希连接"></span>
    <a href="#221-%e5%93%88%e5%b8%8c%e8%bf%9e%e6%8e%a5" class="subheading-anchor" aria-label="Permalink for this section"></a></h2><h3>22.1.1 一阶段哈希连接<span class="hx-absolute -hx-mt-20" id="2211-一阶段哈希连接"></span>
    <a href="#2211-%e4%b8%80%e9%98%b6%e6%ae%b5%e5%93%88%e5%b8%8c%e8%bf%9e%e6%8e%a5" class="subheading-anchor" aria-label="Permalink for this section"></a></h3><p>哈希连接使用预建的哈希表搜索匹配的行。以下是包含这种连接的计划示例：</p>
<div class="hextra-code-block hx-relative hx-mt-6 first:hx-mt-0 hx-group/code">
  

<div><div class="highlight"><pre tabindex="0" class="chroma"><code class="language-bash" data-lang="bash"><span class="line"><span class="cl"><span class="o">=</span>&gt; EXPLAIN <span class="o">(</span>costs off<span class="o">)</span> SELECT *
</span></span><span class="line"><span class="cl">FROM tickets t
</span></span><span class="line"><span class="cl">  JOIN ticket_flights tf ON tf.ticket_no <span class="o">=</span> t.ticket_no<span class="p">;</span>
</span></span><span class="line"><span class="cl">                QUERY PLAN
</span></span><span class="line"><span class="cl">−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−
</span></span><span class="line"><span class="cl"> Hash Join
</span></span><span class="line"><span class="cl">   Hash Cond: <span class="o">(</span>tf.ticket_no <span class="o">=</span> t.ticket_no<span class="o">)</span>
</span></span><span class="line"><span class="cl">   −&gt; Seq Scan on ticket_flights tf
</span></span><span class="line"><span class="cl">   −&gt; Hash
</span></span><span class="line"><span class="cl">       −&gt; Seq Scan on tickets t
</span></span><span class="line"><span class="cl"><span class="o">(</span><span class="m">5</span> rows<span class="o">)</span></span></span></code></pre></div></div><div class="hextra-code-copy-btn-container hx-opacity-0 hx-transition group-hover/code:hx-opacity-100 hx-flex hx-gap-1 hx-absolute hx-m-[11px] hx-right-0 hx-top-0">
  <button
    class="hextra-code-copy-btn hx-group/copybtn hx-transition-all active:hx-opacity-50 hx-bg-primary-700/5 hx-border hx-border-black/5 hx-text-gray-600 hover:hx-text-gray-900 hx-rounded-md hx-p-1.5 dark:hx-bg-primary-300/10 dark:hx-border-white/10 dark:hx-text-gray-400 dark:hover:hx-text-gray-50"
    title="Copy code"
  >
    <div class="copy-icon group-[.copied]/copybtn:hx-hidden hx-pointer-events-none hx-h-4 hx-w-4"></div>
    <div class="success-icon hx-hidden group-[.copied]/copybtn:hx-block hx-pointer-events-none hx-h-4 hx-w-4"></div>
  </button>
</div>

  
</div>
<p>在第一阶段，Hash Join 节点 <sup id="fnref:1"><a href="#fn:1" class="footnote-ref" role="doc-noteref">1</a></sup> 调用 Hash 节点 <sup id="fnref:2"><a href="#fn:2" class="footnote-ref" role="doc-noteref">2</a></sup>，后者从其子节点取出整个内层数据集并将其放入哈希表中。</p>
<p>哈希表存储成对的哈希键和值，通过键可以快速访问值；搜索时间不依赖于哈希表的大小，因为哈希键或多或少均匀地分布在有限数量的桶之间。给定一个键，其所属的桶由哈希键的哈希函数决定；由于桶的数量始终是 2 的幂，只需取计算值的所需位数即可。</p>
<p>如缓冲区缓存一样，这种实现使用了一个可动态扩展的哈希表，通过链解决哈希冲突。<sup id="fnref:3"><a href="#fn:3" class="footnote-ref" role="doc-noteref">3</a></sup></p>
<p>在连接操作的第一阶段，会扫描内层数据集，并针对每一行计算哈希函数。连接条件 (Hash Cond) 中引用的列作为哈希键，而哈希表本身存储了内层数据集中所有的查询字段。</p>
<p>如果<span class="marginalia" data-note="v. 13">整个哈希表可以容纳在 RAM 中</span>，那么哈希连接最为高效，因为在这种情况下，执行器能够在一个批次内处理数据。所分配的内存块大小受到 <span class="marginalia" data-note="4MB"><em>work_mem</em></span> × <span class="marginalia" data-note="1.0"><em>hash_mem_multiplier</em></span> 的限制。</p>
<img src="22-1.png" style="width:70%; float:left" />
<div style="clear:both;"></div>
<p>让我们运行 EXPLAIN ANALYZE 来查看有关查询内存使用情况的统计数据：</p>
<div class="hextra-code-block hx-relative hx-mt-6 first:hx-mt-0 hx-group/code">
  

<div><div class="highlight"><pre tabindex="0" class="chroma"><code class="language-bash" data-lang="bash"><span class="line"><span class="cl"><span class="o">=</span>&gt; SET <span class="nv">work_mem</span> <span class="o">=</span> <span class="s1">&#39;256MB&#39;</span><span class="p">;</span>
</span></span><span class="line"><span class="cl"><span class="o">=</span>&gt; EXPLAIN <span class="o">(</span>analyze, costs off, timing off, summary off<span class="o">)</span>
</span></span><span class="line"><span class="cl">SELECT * FROM bookings b
</span></span><span class="line"><span class="cl">  JOIN tickets t ON b.book_ref <span class="o">=</span> t.book_ref<span class="p">;</span>
</span></span><span class="line"><span class="cl">                          QUERY PLAN
</span></span><span class="line"><span class="cl">−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−
</span></span><span class="line"><span class="cl"> Hash Join <span class="o">(</span>actual <span class="nv">rows</span><span class="o">=</span><span class="m">2949857</span> <span class="nv">loops</span><span class="o">=</span>1<span class="o">)</span>
</span></span><span class="line"><span class="cl">   Hash Cond: <span class="o">(</span>t.book_ref <span class="o">=</span> b.book_ref<span class="o">)</span>
</span></span><span class="line"><span class="cl">   −&gt; Seq Scan on tickets t <span class="o">(</span>actual <span class="nv">rows</span><span class="o">=</span><span class="m">2949857</span> <span class="nv">loops</span><span class="o">=</span>1<span class="o">)</span>
</span></span><span class="line"><span class="cl">   −&gt; Hash <span class="o">(</span>actual <span class="nv">rows</span><span class="o">=</span><span class="m">2111110</span> <span class="nv">loops</span><span class="o">=</span>1<span class="o">)</span>
</span></span><span class="line"><span class="cl">       Buckets: <span class="m">4194304</span> Batches: <span class="m">1</span> Memory Usage: 145986kB
</span></span><span class="line"><span class="cl">       −&gt; Seq Scan on bookings b <span class="o">(</span>actual <span class="nv">rows</span><span class="o">=</span><span class="m">2111110</span> <span class="nv">loops</span><span class="o">=</span>1<span class="o">)</span>
</span></span><span class="line"><span class="cl"><span class="o">(</span><span class="m">6</span> rows<span class="o">)</span></span></span></code></pre></div></div><div class="hextra-code-copy-btn-container hx-opacity-0 hx-transition group-hover/code:hx-opacity-100 hx-flex hx-gap-1 hx-absolute hx-m-[11px] hx-right-0 hx-top-0">
  <button
    class="hextra-code-copy-btn hx-group/copybtn hx-transition-all active:hx-opacity-50 hx-bg-primary-700/5 hx-border hx-border-black/5 hx-text-gray-600 hover:hx-text-gray-900 hx-rounded-md hx-p-1.5 dark:hx-bg-primary-300/10 dark:hx-border-white/10 dark:hx-text-gray-400 dark:hover:hx-text-gray-50"
    title="Copy code"
  >
    <div class="copy-icon group-[.copied]/copybtn:hx-hidden hx-pointer-events-none hx-h-4 hx-w-4"></div>
    <div class="success-icon hx-hidden group-[.copied]/copybtn:hx-block hx-pointer-events-none hx-h-4 hx-w-4"></div>
  </button>
</div>

  
</div>
<p>与以不同方式处理内层数据集和外层数据集的嵌套循环连接不同，哈希连接可以交换它们。较小的数据集通常用作内层数据集，因为这样生成的哈希表较小。</p>
<p>在此例中，整个表适合所分配的缓存：它大约需要 143MB (Memory Usgae) 并包含 4M = 2²² 个桶。因此，连接在一个阶段内完成 (Batches)。</p>
<p>但是，如果查询仅引用一列，那么哈希表将占用 111 MB：</p>
<div class="hextra-code-block hx-relative hx-mt-6 first:hx-mt-0 hx-group/code">
  

<div><div class="highlight"><pre tabindex="0" class="chroma"><code class="language-bash" data-lang="bash"><span class="line"><span class="cl"><span class="o">=</span>&gt; EXPLAIN <span class="o">(</span>analyze, costs off, timing off, summary off<span class="o">)</span>
</span></span><span class="line"><span class="cl">SELECT b.book_ref
</span></span><span class="line"><span class="cl">FROM bookings b
</span></span><span class="line"><span class="cl">  JOIN tickets t ON b.book_ref <span class="o">=</span> t.book_ref<span class="p">;</span>
</span></span><span class="line"><span class="cl">                          QUERY PLAN
</span></span><span class="line"><span class="cl">−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−
</span></span><span class="line"><span class="cl"> Hash Join <span class="o">(</span>actual <span class="nv">rows</span><span class="o">=</span><span class="m">2949857</span> <span class="nv">loops</span><span class="o">=</span>1<span class="o">)</span>
</span></span><span class="line"><span class="cl">   Hash Cond: <span class="o">(</span>t.book_ref <span class="o">=</span> b.book_ref<span class="o">)</span>
</span></span><span class="line"><span class="cl">   −&gt; Index Only Scan using tickets_book_ref_idx on tickets t
</span></span><span class="line"><span class="cl">       <span class="o">(</span>actual <span class="nv">rows</span><span class="o">=</span><span class="m">2949857</span> <span class="nv">loops</span><span class="o">=</span>1<span class="o">)</span>
</span></span><span class="line"><span class="cl">       Heap Fetches: <span class="m">0</span>
</span></span><span class="line"><span class="cl">   −&gt; Hash <span class="o">(</span>actual <span class="nv">rows</span><span class="o">=</span><span class="m">2111110</span> <span class="nv">loops</span><span class="o">=</span>1<span class="o">)</span>
</span></span><span class="line"><span class="cl">       Buckets: <span class="m">4194304</span> Batches: <span class="m">1</span> Memory Usage: 113172kB
</span></span><span class="line"><span class="cl">       −&gt; Seq Scan on bookings b <span class="o">(</span>actual <span class="nv">rows</span><span class="o">=</span><span class="m">2111110</span> <span class="nv">loops</span><span class="o">=</span>1<span class="o">)</span>
</span></span><span class="line"><span class="cl"><span class="o">(</span><span class="m">8</span> rows<span class="o">)</span>
</span></span><span class="line"><span class="cl"><span class="o">=</span>&gt; RESET work_mem<span class="p">;</span></span></span></code></pre></div></div><div class="hextra-code-copy-btn-container hx-opacity-0 hx-transition group-hover/code:hx-opacity-100 hx-flex hx-gap-1 hx-absolute hx-m-[11px] hx-right-0 hx-top-0">
  <button
    class="hextra-code-copy-btn hx-group/copybtn hx-transition-all active:hx-opacity-50 hx-bg-primary-700/5 hx-border hx-border-black/5 hx-text-gray-600 hover:hx-text-gray-900 hx-rounded-md hx-p-1.5 dark:hx-bg-primary-300/10 dark:hx-border-white/10 dark:hx-text-gray-400 dark:hover:hx-text-gray-50"
    title="Copy code"
  >
    <div class="copy-icon group-[.copied]/copybtn:hx-hidden hx-pointer-events-none hx-h-4 hx-w-4"></div>
    <div class="success-icon hx-hidden group-[.copied]/copybtn:hx-block hx-pointer-events-none hx-h-4 hx-w-4"></div>
  </button>
</div>

  
</div>
<p>这是避免在查询中引用多余字段的另外一个原因 (例如，如果你使用 * ，就会发生这种情况)。</p>
<p>选择的桶数应该保证当哈希表完全填满数据时，每个桶平均只容纳一行。更高的密度会增加哈希冲突的概率，导致搜索效率降低，而不太紧实的哈希表会占用太多内存。桶的预估数量会增加到最接近的 2 的幂次。<sup id="fnref:4"><a href="#fn:4" class="footnote-ref" role="doc-noteref">4</a></sup></p>
<p>(如果哈希表的预估大小超过了基于单行平均宽度的内存限制，将使用两阶段哈希)。</p>
<p>在哈希表完全构建之前，哈希连接无法返回结果。</p>
<p>在第二阶段 (此时哈希表已经构建完成)，Hash Join 节点调用第二个子节点以获取外层数据集。对于每个扫描的行，都会在哈希表中搜索匹配项。这需要为包含在连接条件中的外层数据集的列计算哈希键。</p>
<img src="22-2.png" style="width:70%; float:left" />
<div style="clear:both;"></div>
<p>找到的匹配项返回给父节点。</p>
<p><strong>成本估算</strong>。我们已经介绍了基数估算；由于它不依赖于连接方法，所以我现在将重点放在成本估算上面。</p>
<p>Hash 节点的成本由其子节点的总成本表示。它是一个虚拟数字，仅仅填充计划中的槽。<sup id="fnref:5"><a href="#fn:5" class="footnote-ref" role="doc-noteref">5</a></sup> 所有实际估算都包含在 Hash Join 节点的成本中。<sup id="fnref:6"><a href="#fn:6" class="footnote-ref" role="doc-noteref">6</a></sup></p>
<p>此处是一个例子：</p>
<div class="hextra-code-block hx-relative hx-mt-6 first:hx-mt-0 hx-group/code">
  

<div><div class="highlight"><pre tabindex="0" class="chroma"><code class="language-bash" data-lang="bash"><span class="line"><span class="cl"><span class="o">=</span>&gt; EXPLAIN <span class="o">(</span>analyze, timing off, summary off<span class="o">)</span>
</span></span><span class="line"><span class="cl">SELECT * FROM flights f
</span></span><span class="line"><span class="cl">  JOIN seats s ON s.aircraft_code <span class="o">=</span> f.aircraft_code<span class="p">;</span>
</span></span><span class="line"><span class="cl">                            QUERY PLAN
</span></span><span class="line"><span class="cl">−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−
</span></span><span class="line"><span class="cl"> Hash Join <span class="o">(</span><span class="nv">cost</span><span class="o">=</span>38.13..278507.28 <span class="nv">rows</span><span class="o">=</span><span class="m">16518865</span> <span class="nv">width</span><span class="o">=</span>78<span class="o">)</span>
</span></span><span class="line"><span class="cl">   <span class="o">(</span>actual <span class="nv">rows</span><span class="o">=</span><span class="m">16518865</span> <span class="nv">loops</span><span class="o">=</span>1<span class="o">)</span>
</span></span><span class="line"><span class="cl">   Hash Cond: <span class="o">(</span>f.aircraft_code <span class="o">=</span> s.aircraft_code<span class="o">)</span>
</span></span><span class="line"><span class="cl">   −&gt; Seq Scan on flights f <span class="o">(</span><span class="nv">cost</span><span class="o">=</span>0.00..4772.67 <span class="nv">rows</span><span class="o">=</span><span class="m">214867</span> widt...
</span></span><span class="line"><span class="cl">       <span class="o">(</span>actual <span class="nv">rows</span><span class="o">=</span><span class="m">214867</span> <span class="nv">loops</span><span class="o">=</span>1<span class="o">)</span>
</span></span><span class="line"><span class="cl">   −&gt; Hash <span class="o">(</span><span class="nv">cost</span><span class="o">=</span>21.39..21.39 <span class="nv">rows</span><span class="o">=</span><span class="m">1339</span> <span class="nv">width</span><span class="o">=</span>15<span class="o">)</span>
</span></span><span class="line"><span class="cl">       <span class="o">(</span>actual <span class="nv">rows</span><span class="o">=</span><span class="m">1339</span> <span class="nv">loops</span><span class="o">=</span>1<span class="o">)</span>
</span></span><span class="line"><span class="cl">       Buckets: <span class="m">2048</span> Batches: <span class="m">1</span> Memory Usage: 79kB
</span></span><span class="line"><span class="cl">       −&gt; Seq Scan on seats s <span class="o">(</span><span class="nv">cost</span><span class="o">=</span>0.00..21.39 <span class="nv">rows</span><span class="o">=</span><span class="m">1339</span> <span class="nv">width</span><span class="o">=</span>15<span class="o">)</span>
</span></span><span class="line"><span class="cl">           <span class="o">(</span>actual <span class="nv">rows</span><span class="o">=</span><span class="m">1339</span> <span class="nv">loops</span><span class="o">=</span>1<span class="o">)</span>
</span></span><span class="line"><span class="cl"><span class="o">(</span><span class="m">10</span> rows<span class="o">)</span></span></span></code></pre></div></div><div class="hextra-code-copy-btn-container hx-opacity-0 hx-transition group-hover/code:hx-opacity-100 hx-flex hx-gap-1 hx-absolute hx-m-[11px] hx-right-0 hx-top-0">
  <button
    class="hextra-code-copy-btn hx-group/copybtn hx-transition-all active:hx-opacity-50 hx-bg-primary-700/5 hx-border hx-border-black/5 hx-text-gray-600 hover:hx-text-gray-900 hx-rounded-md hx-p-1.5 dark:hx-bg-primary-300/10 dark:hx-border-white/10 dark:hx-text-gray-400 dark:hover:hx-text-gray-50"
    title="Copy code"
  >
    <div class="copy-icon group-[.copied]/copybtn:hx-hidden hx-pointer-events-none hx-h-4 hx-w-4"></div>
    <div class="success-icon hx-hidden group-[.copied]/copybtn:hx-block hx-pointer-events-none hx-h-4 hx-w-4"></div>
  </button>
</div>

  
</div>
<p>连接的启动成本主要反映了创建哈希表的成本，包括以下部分：</p>
<ul>
<li>获取内层数据集的总成本，这是构建哈希表所需的</li>
<li>对于内层数据集的每一行，计算所有包含在连接键中的列的哈希函数成本 (每次操作预估为 <span class="marginalia" data-note="0.0025"><em>cpu_operator_cost</em></span>)</li>
<li>将所有内层数据集中的行插入到哈希表的成本 (每个插入的行预估为 <span class="marginalia" data-note="0.01"><em>cpu_tuple_cost</em></span>)</li>
<li>获取外层数据集的启动成本，这是开始连接操作所需的</li>
</ul>
<p>总成本包括启动成本和连接本身的成本，即：</p>
<ul>
<li>对于外层数据集的每一行，计算所有包含在连接键中的列的哈希函数成本 (<em>cpu_operator_cost</em>)</li>
<li>重新检查连接条件的成本，这是为了解决可能的哈希冲突 (每检查一个操作符预估为 <em>cpu_operator_cost</em>)</li>
<li>处理每个结果行的成本 (<em>cpu_tuple_cost</em>)</li>
</ul>
<p>需要重新检查的次数是最难评估的。它是通过将外层数据集的行数乘以内层数据集的一部分 (存储在哈希表中) 计算而来的。为了评估这个比例，规划器必须考虑到数据分布可能是不均匀的。我将省略这些计算细节；<sup id="fnref:7"><a href="#fn:7" class="footnote-ref" role="doc-noteref">7</a></sup> 在这个特定案例中，这个比例估计为 0.150112。</p>
<p>因此，查询的成本估算如下：</p>
<div class="hextra-code-block hx-relative hx-mt-6 first:hx-mt-0 hx-group/code">
  

<div><div class="highlight"><pre tabindex="0" class="chroma"><code class="language-bash" data-lang="bash"><span class="line"><span class="cl"><span class="o">=</span>&gt; WITH cost<span class="o">(</span>startup<span class="o">)</span> AS <span class="o">(</span>
</span></span><span class="line"><span class="cl">  SELECT round<span class="o">((</span>
</span></span><span class="line"><span class="cl">    21.39 +
</span></span><span class="line"><span class="cl">    current_setting<span class="o">(</span><span class="s1">&#39;cpu_operator_cost&#39;</span><span class="o">)</span>::real * <span class="m">1339</span> +
</span></span><span class="line"><span class="cl">    current_setting<span class="o">(</span><span class="s1">&#39;cpu_tuple_cost&#39;</span><span class="o">)</span>::real * <span class="m">1339</span> +
</span></span><span class="line"><span class="cl">    0.00
</span></span><span class="line"><span class="cl">  <span class="o">)</span>::numeric, 2<span class="o">)</span>
</span></span><span class="line"><span class="cl"><span class="o">)</span>
</span></span><span class="line"><span class="cl">SELECT startup,
</span></span><span class="line"><span class="cl">  startup + round<span class="o">((</span>
</span></span><span class="line"><span class="cl">    4772.67 +
</span></span><span class="line"><span class="cl">    current_setting<span class="o">(</span><span class="s1">&#39;cpu_operator_cost&#39;</span><span class="o">)</span>::real * <span class="m">214867</span> +
</span></span><span class="line"><span class="cl">    current_setting<span class="o">(</span><span class="s1">&#39;cpu_operator_cost&#39;</span><span class="o">)</span>::real * <span class="m">214867</span> * <span class="m">1339</span> *
</span></span><span class="line"><span class="cl">      0.150112 +
</span></span><span class="line"><span class="cl">    current_setting<span class="o">(</span><span class="s1">&#39;cpu_tuple_cost&#39;</span><span class="o">)</span>::real * <span class="m">16518865</span>
</span></span><span class="line"><span class="cl">  <span class="o">)</span>::numeric, 2<span class="o">)</span> AS total
</span></span><span class="line"><span class="cl">FROM cost<span class="p">;</span>
</span></span><span class="line"><span class="cl"> startup <span class="p">|</span> 	 total
</span></span><span class="line"><span class="cl">−−−−−−−−−+−−−−−−−−−−−
</span></span><span class="line"><span class="cl">   38.13 <span class="p">|</span> 278507.26
</span></span><span class="line"><span class="cl"><span class="o">(</span><span class="m">1</span> row<span class="o">)</span></span></span></code></pre></div></div><div class="hextra-code-copy-btn-container hx-opacity-0 hx-transition group-hover/code:hx-opacity-100 hx-flex hx-gap-1 hx-absolute hx-m-[11px] hx-right-0 hx-top-0">
  <button
    class="hextra-code-copy-btn hx-group/copybtn hx-transition-all active:hx-opacity-50 hx-bg-primary-700/5 hx-border hx-border-black/5 hx-text-gray-600 hover:hx-text-gray-900 hx-rounded-md hx-p-1.5 dark:hx-bg-primary-300/10 dark:hx-border-white/10 dark:hx-text-gray-400 dark:hover:hx-text-gray-50"
    title="Copy code"
  >
    <div class="copy-icon group-[.copied]/copybtn:hx-hidden hx-pointer-events-none hx-h-4 hx-w-4"></div>
    <div class="success-icon hx-hidden group-[.copied]/copybtn:hx-block hx-pointer-events-none hx-h-4 hx-w-4"></div>
  </button>
</div>

  
</div>
<p>此处是依赖图：</p>
<img src="22-3.png" style="width:70%; float:left" />
<div style="clear:both;"></div>
<h3>22.1.2 两阶段哈希连接<span class="hx-absolute -hx-mt-20" id="2212-两阶段哈希连接"></span>
    <a href="#2212-%e4%b8%a4%e9%98%b6%e6%ae%b5%e5%93%88%e5%b8%8c%e8%bf%9e%e6%8e%a5" class="subheading-anchor" aria-label="Permalink for this section"></a></h3><p>如果规划器的评估结果表明哈希表不适合所分配的内存，那么内层数据集将被分成若干批次，分别处理。批次数量 (就像桶的数量一样) 总是 2 的幂次；使用哪个批次由哈希键的相应位数决定。<sup id="fnref:8"><a href="#fn:8" class="footnote-ref" role="doc-noteref">8</a></sup></p>
<p>任何两个匹配的行均属于同一个批次：放入不同批次的行不能有相同的哈希码。</p>
<p>所有批次都包含相同数量的哈希键。如果数据分布均匀，批次的大小也将大致相同。规划器可以通过选择合适的批次数量来控制内存消耗。<sup id="fnref:9"><a href="#fn:9" class="footnote-ref" role="doc-noteref">9</a></sup></p>
<p>在第一阶段，执行器扫描内层数据集以构建哈希表。如果扫描的行属于第一批次，则将其添加到哈希表并保存在 RAM 中。否则，它将被写入临时文件 (每个批次都有一个单独的文件)。<sup id="fnref:10"><a href="#fn:10" class="footnote-ref" role="doc-noteref">10</a></sup></p>
<blockquote>
<p>会话可以存储在磁盘上的临时文件总量受 <span class="marginalia" data-note="-1"><em>temp_file_limit</em></span> 参数的限制 (临时表不包括在此限制中)。一旦会话达到此限制，查询便会中止。</p>
</blockquote>
<img src="22-4.png" style="width:70%; float:left" />
<div style="clear:both;"></div>
<p>第二阶段，执行器扫描外层数据集。如果行属于第一批次，它将与包含内层数据集第一批次行的哈希表进行匹配 (无论如何，其他批次中都不会有匹配)。</p>
<p>如果行属于不同的批次，它将存储在一个临时文件中，这个临时文件再次为每个批次单独创建。因此，<em>N</em> 个批次可以使用 2(<em>N</em> − 1) 个文件 (如果某些批次是空的，则更少)。</p>
<p>一旦第二阶段完成，为哈希表分配的内存被释放。此时，我们已经得到了其中一个批次的连接结果。</p>
<img src="22-5.png" style="width:70%; float:left" />
<div style="clear:both;"></div>
<p>对于保存在磁盘上的每个批次，都会重复这两个阶段：内层数据集中的行从临时文件转移到哈希表；然后从另一个临时文件中读取外层数据集中与同一批次相关的行，并与该哈希表进行匹配。一旦处理完毕，临时文件就会被删除。</p>
<img src="22-6.png" style="width:70%; float:left" />
<div style="clear:both;"></div>
<p>与一阶段连接的类似输出不同，两阶段的 EXPLAIN 命令的输出会包含多个批次。如果使用 BUFFERS 选项运行，这个命令还会显示磁盘访问的统计数据：</p>
<div class="hextra-code-block hx-relative hx-mt-6 first:hx-mt-0 hx-group/code">
  

<div><div class="highlight"><pre tabindex="0" class="chroma"><code class="language-bash" data-lang="bash"><span class="line"><span class="cl"><span class="o">=</span>&gt; EXPLAIN <span class="o">(</span>analyze, buffers, costs off, timing off, summary off<span class="o">)</span>
</span></span><span class="line"><span class="cl">SELECT *
</span></span><span class="line"><span class="cl">FROM bookings b
</span></span><span class="line"><span class="cl">  JOIN tickets t ON b.book_ref <span class="o">=</span> t.book_ref<span class="p">;</span>
</span></span><span class="line"><span class="cl">                          QUERY PLAN
</span></span><span class="line"><span class="cl">−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−
</span></span><span class="line"><span class="cl"> Hash Join <span class="o">(</span>actual <span class="nv">rows</span><span class="o">=</span><span class="m">2949857</span> <span class="nv">loops</span><span class="o">=</span>1<span class="o">)</span>
</span></span><span class="line"><span class="cl">   Hash Cond: <span class="o">(</span>t.book_ref <span class="o">=</span> b.book_ref<span class="o">)</span>
</span></span><span class="line"><span class="cl">   Buffers: shared <span class="nv">hit</span><span class="o">=</span><span class="m">7236</span> <span class="nv">read</span><span class="o">=</span>55626, temp <span class="nv">read</span><span class="o">=</span><span class="m">55126</span>
</span></span><span class="line"><span class="cl">   <span class="nv">written</span><span class="o">=</span><span class="m">55126</span>
</span></span><span class="line"><span class="cl">   −&gt; Seq Scan on tickets t <span class="o">(</span>actual <span class="nv">rows</span><span class="o">=</span><span class="m">2949857</span> <span class="nv">loops</span><span class="o">=</span>1<span class="o">)</span>
</span></span><span class="line"><span class="cl">       Buffers: shared <span class="nv">read</span><span class="o">=</span><span class="m">49415</span>
</span></span><span class="line"><span class="cl">   −&gt; Hash <span class="o">(</span>actual <span class="nv">rows</span><span class="o">=</span><span class="m">2111110</span> <span class="nv">loops</span><span class="o">=</span>1<span class="o">)</span>
</span></span><span class="line"><span class="cl">       Buckets: <span class="m">65536</span> Batches: <span class="m">64</span> Memory Usage: 2277kB
</span></span><span class="line"><span class="cl">       Buffers: shared <span class="nv">hit</span><span class="o">=</span><span class="m">7236</span> <span class="nv">read</span><span class="o">=</span>6211, temp <span class="nv">written</span><span class="o">=</span><span class="m">10858</span>
</span></span><span class="line"><span class="cl">       −&gt; Seq Scan on bookings b <span class="o">(</span>actual <span class="nv">rows</span><span class="o">=</span><span class="m">2111110</span> <span class="nv">loops</span><span class="o">=</span>1<span class="o">)</span>
</span></span><span class="line"><span class="cl">           Buffers: shared <span class="nv">hit</span><span class="o">=</span><span class="m">7236</span> <span class="nv">read</span><span class="o">=</span><span class="m">6211</span>
</span></span><span class="line"><span class="cl"><span class="o">(</span><span class="m">11</span> rows<span class="o">)</span></span></span></code></pre></div></div><div class="hextra-code-copy-btn-container hx-opacity-0 hx-transition group-hover/code:hx-opacity-100 hx-flex hx-gap-1 hx-absolute hx-m-[11px] hx-right-0 hx-top-0">
  <button
    class="hextra-code-copy-btn hx-group/copybtn hx-transition-all active:hx-opacity-50 hx-bg-primary-700/5 hx-border hx-border-black/5 hx-text-gray-600 hover:hx-text-gray-900 hx-rounded-md hx-p-1.5 dark:hx-bg-primary-300/10 dark:hx-border-white/10 dark:hx-text-gray-400 dark:hover:hx-text-gray-50"
    title="Copy code"
  >
    <div class="copy-icon group-[.copied]/copybtn:hx-hidden hx-pointer-events-none hx-h-4 hx-w-4"></div>
    <div class="success-icon hx-hidden group-[.copied]/copybtn:hx-block hx-pointer-events-none hx-h-4 hx-w-4"></div>
  </button>
</div>

  
</div>
<p>我已经在上面展示了这个查询，并增加了 <em>work_mem</em> 设置。4MB 的默认值太小，整个哈希表无法全部放入 RAM 中；在本例中，数据被分成 64 个批次，并且哈希表使用了 64K = 2<sup>16</sup> 个桶。随着哈希表的构建 (Hash 节点) ，数据被写入临时文件 (temp written)；在连接阶段 (Hash Join 节点)，临时文件被读取和写入 (temp read, written)。</p>
<p>要收集更多关于临时文件的统计数据，你可以将 <span class="marginalia" data-note="-1"><em>log_temp_files</em></span> 参数设为零。然后服务器日志将列出所有的临时文件和其大小 (在删除临时文件时列出)。</p>
<h3>22.1.3 动态调整<span class="hx-absolute -hx-mt-20" id="2213-动态调整"></span>
    <a href="#2213-%e5%8a%a8%e6%80%81%e8%b0%83%e6%95%b4" class="subheading-anchor" aria-label="Permalink for this section"></a></h3><p>计划的事件流程可能会因为两个问题而受到干扰：不准确的统计信息和不均匀的数据分布。</p>
<p>如果连接键列中的值分布不均，那么不同的批次将具有不同的大小。</p>
<p>如果某个批次 (第一个批次除外) 变得过大，其所有行都必须写入磁盘，然后再从磁盘读取。外层数据集会造成大部分问题，因为它通常更大。因此，如果外层数据集的 MCV 上有常规的、非多元统计信息 (也就是说，外层数据集由一个表表示，并且连接是通过单列执行的)，具有与 MCV 相对应的哈希码的行被认为是第一批次的一部分。<sup id="fnref:11"><a href="#fn:11" class="footnote-ref" role="doc-noteref">11</a></sup> 这种技术 (称为倾斜优化) 可以在一定程度上减少两阶段连接的 I/O 开销。</p>
<p>由于这两个因素，一些 (或全部) 批次的大小可能会超过预估值。然后，相应的哈希表将不适合所分配的内存块，并超过定义的限制。</p>
<p>因此，如果正在构建的哈希表变得太大，批次的数量会即时增加 (翻倍)。每个批次实际上被分成两个新的批次：大约一半的行 (假设分布是均匀的) 留在哈希表中，而另一半被保存到一个新的临时文件中。<sup id="fnref:12"><a href="#fn:12" class="footnote-ref" role="doc-noteref">12</a></sup></p>
<p>即使最初规划的是一阶段连接，这种拆分也可能会发生。事实上，一阶段连接和两阶段连接使用同一个算法实现，由相同的代码实现；我在这里单独提出它们，仅仅是为了让叙述更加流畅。</p>
<p>批次数量无法减少。如果事实证明规划器高估了数据大小，批次也不会被合并在一起。</p>
<p>在分布不均的情况下，增加批次数量可能无济于事。例如，如果在所有行中，键列都包含一个相同的值，那么它们将被放入同一批次中，因为哈希函数将一次又一次返回相同的值。不幸的是，在这种情况下，哈希表将继续增长，不管施加了什么限制。</p>
<blockquote>
<p>理论上，这个问题可以通过多阶段连接来解决，它会对批次执行部分扫描，但尚不支持。</p>
</blockquote>
<p>为了演示批次数量的动态增加，我们得先执行一些操作：</p>
<div class="hextra-code-block hx-relative hx-mt-6 first:hx-mt-0 hx-group/code">
  

<div><div class="highlight"><pre tabindex="0" class="chroma"><code class="language-bash" data-lang="bash"><span class="line"><span class="cl"><span class="o">=</span>&gt; CREATE TABLE bookings_copy <span class="o">(</span>LIKE bookings INCLUDING INDEXES<span class="o">)</span>
</span></span><span class="line"><span class="cl">WITH <span class="o">(</span><span class="nv">autovacuum_enabled</span> <span class="o">=</span> off<span class="o">)</span><span class="p">;</span>
</span></span><span class="line"><span class="cl"><span class="o">=</span>&gt; INSERT INTO bookings_copy SELECT * FROM bookings<span class="p">;</span>
</span></span><span class="line"><span class="cl">INSERT <span class="m">0</span> <span class="nv">2111110</span>
</span></span><span class="line"><span class="cl"><span class="o">=</span>&gt; DELETE FROM bookings_copy WHERE random<span class="o">()</span> &lt; 0.9<span class="p">;</span>
</span></span><span class="line"><span class="cl">DELETE <span class="nv">1899232</span>
</span></span><span class="line"><span class="cl"><span class="o">=</span>&gt; ANALYZE bookings_copy<span class="p">;</span>
</span></span><span class="line"><span class="cl"><span class="o">=</span>&gt; INSERT INTO bookings_copy SELECT * FROM bookings
</span></span><span class="line"><span class="cl">ON CONFLICT DO NOTHING<span class="p">;</span>
</span></span><span class="line"><span class="cl">INSERT <span class="m">0</span> <span class="nv">1899232</span>
</span></span><span class="line"><span class="cl"><span class="o">=</span>&gt; SELECT reltuples FROM pg_class WHERE <span class="nv">relname</span> <span class="o">=</span> <span class="s1">&#39;bookings_copy&#39;</span><span class="p">;</span>
</span></span><span class="line"><span class="cl"> reltuples
</span></span><span class="line"><span class="cl">−−−−−−−−−−−
</span></span><span class="line"><span class="cl">    <span class="m">211878</span>
</span></span><span class="line"><span class="cl"><span class="o">(</span><span class="m">1</span> row<span class="o">)</span></span></span></code></pre></div></div><div class="hextra-code-copy-btn-container hx-opacity-0 hx-transition group-hover/code:hx-opacity-100 hx-flex hx-gap-1 hx-absolute hx-m-[11px] hx-right-0 hx-top-0">
  <button
    class="hextra-code-copy-btn hx-group/copybtn hx-transition-all active:hx-opacity-50 hx-bg-primary-700/5 hx-border hx-border-black/5 hx-text-gray-600 hover:hx-text-gray-900 hx-rounded-md hx-p-1.5 dark:hx-bg-primary-300/10 dark:hx-border-white/10 dark:hx-text-gray-400 dark:hover:hx-text-gray-50"
    title="Copy code"
  >
    <div class="copy-icon group-[.copied]/copybtn:hx-hidden hx-pointer-events-none hx-h-4 hx-w-4"></div>
    <div class="success-icon hx-hidden group-[.copied]/copybtn:hx-block hx-pointer-events-none hx-h-4 hx-w-4"></div>
  </button>
</div>

  
</div>
<p>现在我们得到一个名为 bookings_copy 的新表。它是 bookings 表的精确副本，但规划器对于表中行数低估了十倍。如果为另一个连接操作产生的行集生成哈希表，可能会出现类似的情况，因此没有可靠的统计信息可用。</p>
<p>这种错误计算使规划器认为 8 个桶就足够了，但是在执行连接时，这个数字增长到了 32：</p>
<div class="hextra-code-block hx-relative hx-mt-6 first:hx-mt-0 hx-group/code">
  

<div><div class="highlight"><pre tabindex="0" class="chroma"><code class="language-bash" data-lang="bash"><span class="line"><span class="cl"><span class="o">=</span>&gt; EXPLAIN <span class="o">(</span>analyze, costs off, timing off, summary off<span class="o">)</span>
</span></span><span class="line"><span class="cl">SELECT *
</span></span><span class="line"><span class="cl">FROM bookings_copy b
</span></span><span class="line"><span class="cl">  JOIN tickets t ON b.book_ref <span class="o">=</span> t.book_ref<span class="p">;</span>
</span></span><span class="line"><span class="cl">                             QUERY PLAN
</span></span><span class="line"><span class="cl">−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−
</span></span><span class="line"><span class="cl"> Hash Join <span class="o">(</span>actual <span class="nv">rows</span><span class="o">=</span><span class="m">2949857</span> <span class="nv">loops</span><span class="o">=</span>1<span class="o">)</span>
</span></span><span class="line"><span class="cl">   Hash Cond: <span class="o">(</span>t.book_ref <span class="o">=</span> b.book_ref<span class="o">)</span>
</span></span><span class="line"><span class="cl">   −&gt; Seq Scan on tickets t <span class="o">(</span>actual <span class="nv">rows</span><span class="o">=</span><span class="m">2949857</span> <span class="nv">loops</span><span class="o">=</span>1<span class="o">)</span>
</span></span><span class="line"><span class="cl">   −&gt; Hash <span class="o">(</span>actual <span class="nv">rows</span><span class="o">=</span><span class="m">2111110</span> <span class="nv">loops</span><span class="o">=</span>1<span class="o">)</span>
</span></span><span class="line"><span class="cl">       Buckets: <span class="m">65536</span> <span class="o">(</span>originally 65536<span class="o">)</span> Batches: <span class="m">32</span> <span class="o">(</span>originally 8<span class="o">)</span>
</span></span><span class="line"><span class="cl">        Memory Usage: 4040kB
</span></span><span class="line"><span class="cl">       −&gt; Seq Scan on bookings_copy b <span class="o">(</span>actual <span class="nv">rows</span><span class="o">=</span><span class="m">2111110</span> <span class="nv">loops</span><span class="o">=</span>1<span class="o">)</span>
</span></span><span class="line"><span class="cl"><span class="o">(</span><span class="m">7</span> rows<span class="o">)</span></span></span></code></pre></div></div><div class="hextra-code-copy-btn-container hx-opacity-0 hx-transition group-hover/code:hx-opacity-100 hx-flex hx-gap-1 hx-absolute hx-m-[11px] hx-right-0 hx-top-0">
  <button
    class="hextra-code-copy-btn hx-group/copybtn hx-transition-all active:hx-opacity-50 hx-bg-primary-700/5 hx-border hx-border-black/5 hx-text-gray-600 hover:hx-text-gray-900 hx-rounded-md hx-p-1.5 dark:hx-bg-primary-300/10 dark:hx-border-white/10 dark:hx-text-gray-400 dark:hover:hx-text-gray-50"
    title="Copy code"
  >
    <div class="copy-icon group-[.copied]/copybtn:hx-hidden hx-pointer-events-none hx-h-4 hx-w-4"></div>
    <div class="success-icon hx-hidden group-[.copied]/copybtn:hx-block hx-pointer-events-none hx-h-4 hx-w-4"></div>
  </button>
</div>

  
</div>
<p><strong>成本估算</strong>。我已经使用这个示例演示了一阶段连接的成本估算，但现在我将把可用内存的大小减少到最小，因此规划器将不得不使用两个批次。这增加了连接的成本：</p>
<div class="hextra-code-block hx-relative hx-mt-6 first:hx-mt-0 hx-group/code">
  

<div><div class="highlight"><pre tabindex="0" class="chroma"><code class="language-bash" data-lang="bash"><span class="line"><span class="cl"><span class="o">=</span>&gt; SET <span class="nv">work_mem</span> <span class="o">=</span> <span class="s1">&#39;64kB&#39;</span><span class="p">;</span>
</span></span><span class="line"><span class="cl"><span class="o">=</span>&gt; EXPLAIN <span class="o">(</span>analyze, timing off, summary off<span class="o">)</span>
</span></span><span class="line"><span class="cl">SELECT * FROM flights f
</span></span><span class="line"><span class="cl">  JOIN seats s ON s.aircraft_code <span class="o">=</span> f.aircraft_code<span class="p">;</span>
</span></span><span class="line"><span class="cl">                             QUERY PLAN
</span></span><span class="line"><span class="cl">−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−
</span></span><span class="line"><span class="cl"> Hash Join <span class="o">(</span><span class="nv">cost</span><span class="o">=</span>45.13..283139.28 <span class="nv">rows</span><span class="o">=</span><span class="m">16518865</span> <span class="nv">width</span><span class="o">=</span>78<span class="o">)</span>
</span></span><span class="line"><span class="cl">   <span class="o">(</span>actual <span class="nv">rows</span><span class="o">=</span><span class="m">16518865</span> <span class="nv">loops</span><span class="o">=</span>1<span class="o">)</span>
</span></span><span class="line"><span class="cl">   Hash Cond: <span class="o">(</span>f.aircraft_code <span class="o">=</span> s.aircraft_code<span class="o">)</span>
</span></span><span class="line"><span class="cl">   −&gt; Seq Scan on flights f <span class="o">(</span><span class="nv">cost</span><span class="o">=</span>0.00..4772.67 <span class="nv">rows</span><span class="o">=</span><span class="m">214867</span> widt...
</span></span><span class="line"><span class="cl">        <span class="o">(</span>actual <span class="nv">rows</span><span class="o">=</span><span class="m">214867</span> <span class="nv">loops</span><span class="o">=</span>1<span class="o">)</span>
</span></span><span class="line"><span class="cl">  −&gt; Hash <span class="o">(</span><span class="nv">cost</span><span class="o">=</span>21.39..21.39 <span class="nv">rows</span><span class="o">=</span><span class="m">1339</span> <span class="nv">width</span><span class="o">=</span>15<span class="o">)</span>
</span></span><span class="line"><span class="cl">        <span class="o">(</span>actual <span class="nv">rows</span><span class="o">=</span><span class="m">1339</span> <span class="nv">loops</span><span class="o">=</span>1<span class="o">)</span>
</span></span><span class="line"><span class="cl">        Buckets: <span class="m">2048</span> Batches: <span class="m">2</span> Memory Usage: 55kB
</span></span><span class="line"><span class="cl">        −&gt; Seq Scan on seats s <span class="o">(</span><span class="nv">cost</span><span class="o">=</span>0.00..21.39 <span class="nv">rows</span><span class="o">=</span><span class="m">1339</span> <span class="nv">width</span><span class="o">=</span>15<span class="o">)</span>
</span></span><span class="line"><span class="cl">             <span class="o">(</span>actual <span class="nv">rows</span><span class="o">=</span><span class="m">1339</span> <span class="nv">loops</span><span class="o">=</span>1<span class="o">)</span>
</span></span><span class="line"><span class="cl"><span class="o">(</span><span class="m">10</span> rows<span class="o">)</span>
</span></span><span class="line"><span class="cl"><span class="o">=</span>&gt; RESET work_mem<span class="p">;</span></span></span></code></pre></div></div><div class="hextra-code-copy-btn-container hx-opacity-0 hx-transition group-hover/code:hx-opacity-100 hx-flex hx-gap-1 hx-absolute hx-m-[11px] hx-right-0 hx-top-0">
  <button
    class="hextra-code-copy-btn hx-group/copybtn hx-transition-all active:hx-opacity-50 hx-bg-primary-700/5 hx-border hx-border-black/5 hx-text-gray-600 hover:hx-text-gray-900 hx-rounded-md hx-p-1.5 dark:hx-bg-primary-300/10 dark:hx-border-white/10 dark:hx-text-gray-400 dark:hover:hx-text-gray-50"
    title="Copy code"
  >
    <div class="copy-icon group-[.copied]/copybtn:hx-hidden hx-pointer-events-none hx-h-4 hx-w-4"></div>
    <div class="success-icon hx-hidden group-[.copied]/copybtn:hx-block hx-pointer-events-none hx-h-4 hx-w-4"></div>
  </button>
</div>

  
</div>
<p>第二阶段的成本是将行溢出到临时文件并从这些文件中读取而产生的。</p>
<p>两阶段连接的启动成本基于一阶段连接的启动成本，该成本增加了将所需数量的页面写入磁盘的预估成本，以存储内层数据集所有行的所有必要字段。<sup id="fnref:13"><a href="#fn:13" class="footnote-ref" role="doc-noteref">13</a></sup> 尽管在构建哈希表时第一批次不写入磁盘，但估算没有考虑到这一点，因此不依赖于批次的数量。</p>
<p>总成本包括一阶段连接的总成本和读取之前存储在磁盘上的内层数据集的预估成本，以及读取和写入外层数据集的预估成本。</p>
<p>写入和读取都按每页花费 <em>seq_page_cost</em> 预估，因为假定 I/O 操作是顺序的。</p>
<p>在此特例下，内层数据集所需的页数预估为 7，而外层数据集的数据预估适合 2309 页。将这些估算值与上面计算的一阶段连接的成本相加之后，我们得到了与查询计划中所示的相同数字：</p>
<div class="hextra-code-block hx-relative hx-mt-6 first:hx-mt-0 hx-group/code">
  

<div><div class="highlight"><pre tabindex="0" class="chroma"><code class="language-bash" data-lang="bash"><span class="line"><span class="cl"><span class="o">=</span>&gt; SELECT 38.13 + -- startup cost of a one-pass join
</span></span><span class="line"><span class="cl">  current_setting<span class="o">(</span><span class="s1">&#39;seq_page_cost&#39;</span><span class="o">)</span>::real * <span class="m">7</span>
</span></span><span class="line"><span class="cl">  AS startup,
</span></span><span class="line"><span class="cl">278507.28 + -- total cost of a one-pass join
</span></span><span class="line"><span class="cl">  current_setting<span class="o">(</span><span class="s1">&#39;seq_page_cost&#39;</span><span class="o">)</span>::real * <span class="m">2</span> * <span class="o">(</span><span class="m">7</span> + 2309<span class="o">)</span>
</span></span><span class="line"><span class="cl">  AS total<span class="p">;</span>
</span></span><span class="line"><span class="cl"> startup <span class="p">|</span> 	 total
</span></span><span class="line"><span class="cl">−−−−−−−−−+−−−−−−−−−−−
</span></span><span class="line"><span class="cl">   45.13 <span class="p">|</span> 283139.28
</span></span><span class="line"><span class="cl"><span class="o">(</span><span class="m">1</span> row<span class="o">)</span></span></span></code></pre></div></div><div class="hextra-code-copy-btn-container hx-opacity-0 hx-transition group-hover/code:hx-opacity-100 hx-flex hx-gap-1 hx-absolute hx-m-[11px] hx-right-0 hx-top-0">
  <button
    class="hextra-code-copy-btn hx-group/copybtn hx-transition-all active:hx-opacity-50 hx-bg-primary-700/5 hx-border hx-border-black/5 hx-text-gray-600 hover:hx-text-gray-900 hx-rounded-md hx-p-1.5 dark:hx-bg-primary-300/10 dark:hx-border-white/10 dark:hx-text-gray-400 dark:hover:hx-text-gray-50"
    title="Copy code"
  >
    <div class="copy-icon group-[.copied]/copybtn:hx-hidden hx-pointer-events-none hx-h-4 hx-w-4"></div>
    <div class="success-icon hx-hidden group-[.copied]/copybtn:hx-block hx-pointer-events-none hx-h-4 hx-w-4"></div>
  </button>
</div>

  
</div>
<p>因此，如果内存不足，连接将分两个阶段执行并且效率变得更低。因此，遵循以下几点很重要：</p>
<ul>
<li>查询必须以排除哈希表中多余字段的方式组成。</li>
<li>在构建哈希表时，规划器必须选择两组行中较小的一组。</li>
</ul>
<h3>22.1.4 <span class="marginalia" data-note="v. 9.6"><strong>在并行计划中使用哈希连接</strong></span><span class="hx-absolute -hx-mt-20" id="2214-span-classmarginalia-data-notev-96在并行计划中使用哈希连接span"></span>
    <a href="#2214-span-classmarginalia-data-notev-96%e5%9c%a8%e5%b9%b6%e8%a1%8c%e8%ae%a1%e5%88%92%e4%b8%ad%e4%bd%bf%e7%94%a8%e5%93%88%e5%b8%8c%e8%bf%9e%e6%8e%a5span" class="subheading-anchor" aria-label="Permalink for this section"></a></h3><p>上面描述的哈希连接算法也可以用于并行计划。首先，若干个并行进程各自独立地为内层数据集构建它们自己的 (完全相同的) 哈希表；然后他们开始并行处理外层数据集。此处的性能提升是由于每个进程只扫描其自己份额的外层数据集的行。</p>
<p>以下计划使用常规的一阶段哈希连接：</p>
<div class="hextra-code-block hx-relative hx-mt-6 first:hx-mt-0 hx-group/code">
  

<div><div class="highlight"><pre tabindex="0" class="chroma"><code class="language-bash" data-lang="bash"><span class="line"><span class="cl"><span class="o">=</span>&gt; SET <span class="nv">work_mem</span> <span class="o">=</span> <span class="s1">&#39;128MB&#39;</span><span class="p">;</span>
</span></span><span class="line"><span class="cl">
</span></span><span class="line"><span class="cl"><span class="o">=</span>&gt; SET <span class="nv">enable_parallel_hash</span> <span class="o">=</span> off<span class="p">;</span>
</span></span><span class="line"><span class="cl">
</span></span><span class="line"><span class="cl"><span class="o">=</span>&gt; EXPLAIN <span class="o">(</span>analyze, costs off, timing off, summary off<span class="o">)</span>
</span></span><span class="line"><span class="cl">SELECT count<span class="o">(</span>*<span class="o">)</span>
</span></span><span class="line"><span class="cl">FROM bookings b
</span></span><span class="line"><span class="cl">  JOIN tickets t ON t.book_ref <span class="o">=</span> b.book_ref<span class="p">;</span>
</span></span><span class="line"><span class="cl">                             QUERY PLAN
</span></span><span class="line"><span class="cl">−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−
</span></span><span class="line"><span class="cl"> Finalize Aggregate <span class="o">(</span>actual <span class="nv">rows</span><span class="o">=</span><span class="m">1</span> <span class="nv">loops</span><span class="o">=</span>1<span class="o">)</span>
</span></span><span class="line"><span class="cl">   −&gt; Gather <span class="o">(</span>actual <span class="nv">rows</span><span class="o">=</span><span class="m">3</span> <span class="nv">loops</span><span class="o">=</span>1<span class="o">)</span>
</span></span><span class="line"><span class="cl">       Workers Planned: <span class="m">2</span>
</span></span><span class="line"><span class="cl">       Workers Launched: <span class="m">2</span>
</span></span><span class="line"><span class="cl">       −&gt; Partial Aggregate <span class="o">(</span>actual <span class="nv">rows</span><span class="o">=</span><span class="m">1</span> <span class="nv">loops</span><span class="o">=</span>3<span class="o">)</span>
</span></span><span class="line"><span class="cl">           −&gt; Hash Join <span class="o">(</span>actual <span class="nv">rows</span><span class="o">=</span><span class="m">983286</span> <span class="nv">loops</span><span class="o">=</span>3<span class="o">)</span>
</span></span><span class="line"><span class="cl">               Hash Cond: <span class="o">(</span>t.book_ref <span class="o">=</span> b.book_ref<span class="o">)</span>
</span></span><span class="line"><span class="cl">               −&gt; Parallel Index Only Scan using tickets_book_ref...
</span></span><span class="line"><span class="cl">                   Heap Fetches: <span class="m">0</span>
</span></span><span class="line"><span class="cl">               −&gt; Hash <span class="o">(</span>actual <span class="nv">rows</span><span class="o">=</span><span class="m">2111110</span> <span class="nv">loops</span><span class="o">=</span>3<span class="o">)</span>
</span></span><span class="line"><span class="cl">                   Buckets: <span class="m">4194304</span> Batches: <span class="m">1</span> Memory Usage:
</span></span><span class="line"><span class="cl">                   113172kB
</span></span><span class="line"><span class="cl">                   −&gt; Seq Scan on bookings b <span class="o">(</span>actual <span class="nv">rows</span><span class="o">=</span>2111110...
</span></span><span class="line"><span class="cl"><span class="o">(</span><span class="m">13</span> rows<span class="o">)</span>
</span></span><span class="line"><span class="cl"><span class="o">=</span>&gt; RESET enable_parallel_hash<span class="p">;</span></span></span></code></pre></div></div><div class="hextra-code-copy-btn-container hx-opacity-0 hx-transition group-hover/code:hx-opacity-100 hx-flex hx-gap-1 hx-absolute hx-m-[11px] hx-right-0 hx-top-0">
  <button
    class="hextra-code-copy-btn hx-group/copybtn hx-transition-all active:hx-opacity-50 hx-bg-primary-700/5 hx-border hx-border-black/5 hx-text-gray-600 hover:hx-text-gray-900 hx-rounded-md hx-p-1.5 dark:hx-bg-primary-300/10 dark:hx-border-white/10 dark:hx-text-gray-400 dark:hover:hx-text-gray-50"
    title="Copy code"
  >
    <div class="copy-icon group-[.copied]/copybtn:hx-hidden hx-pointer-events-none hx-h-4 hx-w-4"></div>
    <div class="success-icon hx-hidden group-[.copied]/copybtn:hx-block hx-pointer-events-none hx-h-4 hx-w-4"></div>
  </button>
</div>

  
</div>
<p>此处每个进程对 bookings 表进行哈希处理，然后通过 Parallel Index Only Scan 节点检索其自己份额的外层数据集，并将这些行与哈希表进行匹配。</p>
<p>哈希表内存限制分别应用于每个并行进程，所以为此分配的内存总大小将比计划 (Memoey Usage) 中显示的大三倍。</p>
<h3>22.1.5 <span class="marginalia" data-note="v. 11"><strong>并行一阶段哈希连接</strong></span><span class="hx-absolute -hx-mt-20" id="2215-span-classmarginalia-data-notev-11并行一阶段哈希连接span"></span>
    <a href="#2215-span-classmarginalia-data-notev-11%e5%b9%b6%e8%a1%8c%e4%b8%80%e9%98%b6%e6%ae%b5%e5%93%88%e5%b8%8c%e8%bf%9e%e6%8e%a5span" class="subheading-anchor" aria-label="Permalink for this section"></a></h3><p>尽管常规的哈希连接在并行计划中效率很高 (尤其是对于较小的内层数据集，并行处理对其意义不大)，但更大的数据集更适合由特殊的并行哈希连接算法处理。</p>
<p>并行算法的一个重要区别是，哈希表是在共享内存中创建的，这个内存是动态分配的，所有参与连接操作的并行进程都可以访问。并行处理并不是创建多个独立的哈希表，而是建立了一个共同的哈希表，它使用所有参与进程的专用总内存量。这增加了一阶段完成连接的可能性。</p>
<p>在第一个阶段 (在计划中由 Parallel Hash 节点表示)，所有并行进程并行访问内层数据集，建立了一个共同的哈希表。<sup id="fnref:14"><a href="#fn:14" class="footnote-ref" role="doc-noteref">14</a></sup></p>
<img src="22-7.png" style="width:70%; float:left" />
<div style="clear:both;"></div>
<p>要从此处继续进行，每个并行进程必须完成其在第一阶段处理的份额。<sup id="fnref:15"><a href="#fn:15" class="footnote-ref" role="doc-noteref">15</a></sup></p>
<p>在第二阶段 (Parallel Hash Join 节点)，进程再次并行运行，将它们外层数据集的份额与此时已经构建好的哈希表进行匹配。<sup id="fnref:16"><a href="#fn:16" class="footnote-ref" role="doc-noteref">16</a></sup></p>
<img src="22-8.png" style="width:70%; float:left" />
<div style="clear:both;"></div>
<p>此处是一个此类计划的例子：</p>
<div class="hextra-code-block hx-relative hx-mt-6 first:hx-mt-0 hx-group/code">
  

<div><div class="highlight"><pre tabindex="0" class="chroma"><code class="language-bash" data-lang="bash"><span class="line"><span class="cl"><span class="o">=</span>&gt; SET <span class="nv">work_mem</span> <span class="o">=</span> <span class="s1">&#39;64MB&#39;</span><span class="p">;</span>
</span></span><span class="line"><span class="cl"><span class="o">=</span>&gt; EXPLAIN <span class="o">(</span>analyze, costs off, timing off, summary off<span class="o">)</span>
</span></span><span class="line"><span class="cl">SELECT count<span class="o">(</span>*<span class="o">)</span>
</span></span><span class="line"><span class="cl">FROM bookings b
</span></span><span class="line"><span class="cl">  JOIN tickets t ON t.book_ref <span class="o">=</span> b.book_ref<span class="p">;</span>
</span></span><span class="line"><span class="cl">                             QUERY PLAN
</span></span><span class="line"><span class="cl">−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−
</span></span><span class="line"><span class="cl"> Finalize Aggregate <span class="o">(</span>actual <span class="nv">rows</span><span class="o">=</span><span class="m">1</span> <span class="nv">loops</span><span class="o">=</span>1<span class="o">)</span>
</span></span><span class="line"><span class="cl">   −&gt; Gather <span class="o">(</span>actual <span class="nv">rows</span><span class="o">=</span><span class="m">3</span> <span class="nv">loops</span><span class="o">=</span>1<span class="o">)</span>
</span></span><span class="line"><span class="cl">       Workers Planned: <span class="m">2</span>
</span></span><span class="line"><span class="cl">       Workers Launched: <span class="m">2</span>
</span></span><span class="line"><span class="cl">       −&gt; Partial Aggregate <span class="o">(</span>actual <span class="nv">rows</span><span class="o">=</span><span class="m">1</span> <span class="nv">loops</span><span class="o">=</span>3<span class="o">)</span>
</span></span><span class="line"><span class="cl">           −&gt; Parallel Hash Join <span class="o">(</span>actual <span class="nv">rows</span><span class="o">=</span><span class="m">983286</span> <span class="nv">loops</span><span class="o">=</span>3<span class="o">)</span>
</span></span><span class="line"><span class="cl">           Hash Cond: <span class="o">(</span>t.book_ref <span class="o">=</span> b.book_ref<span class="o">)</span>
</span></span><span class="line"><span class="cl">           −&gt; Parallel Index Only Scan using tickets_book_ref...
</span></span><span class="line"><span class="cl">               Heap Fetches: <span class="m">0</span>
</span></span><span class="line"><span class="cl">           −&gt; Parallel Hash <span class="o">(</span>actual <span class="nv">rows</span><span class="o">=</span><span class="m">703703</span> <span class="nv">loops</span><span class="o">=</span>3<span class="o">)</span>
</span></span><span class="line"><span class="cl">               Buckets: <span class="m">4194304</span> Batches: <span class="m">1</span> Memory Usage:
</span></span><span class="line"><span class="cl">               115392kB
</span></span><span class="line"><span class="cl">               −&gt; Parallel Seq Scan on bookings b <span class="o">(</span>actual row...
</span></span><span class="line"><span class="cl"><span class="o">(</span><span class="m">13</span> rows<span class="o">)</span>
</span></span><span class="line"><span class="cl"><span class="o">=</span>&gt; RESET work_mem<span class="p">;</span></span></span></code></pre></div></div><div class="hextra-code-copy-btn-container hx-opacity-0 hx-transition group-hover/code:hx-opacity-100 hx-flex hx-gap-1 hx-absolute hx-m-[11px] hx-right-0 hx-top-0">
  <button
    class="hextra-code-copy-btn hx-group/copybtn hx-transition-all active:hx-opacity-50 hx-bg-primary-700/5 hx-border hx-border-black/5 hx-text-gray-600 hover:hx-text-gray-900 hx-rounded-md hx-p-1.5 dark:hx-bg-primary-300/10 dark:hx-border-white/10 dark:hx-text-gray-400 dark:hover:hx-text-gray-50"
    title="Copy code"
  >
    <div class="copy-icon group-[.copied]/copybtn:hx-hidden hx-pointer-events-none hx-h-4 hx-w-4"></div>
    <div class="success-icon hx-hidden group-[.copied]/copybtn:hx-block hx-pointer-events-none hx-h-4 hx-w-4"></div>
  </button>
</div>

  
</div>
<p>这与我在上一章节中展示的查询相同，但当时并行哈希连接通过 <em>enable_parallel_hash</em> 参数关闭了。</p>
<p>虽然与之前演示的常规哈希连接相比，可用内存减少了一半，但操作仍然一阶段完成，因为它使用了分配给所有并行进程的内存 (Memory Usage)。哈希表变得稍微大一些，但由于现在我们只有这一个哈希表，总内存使用量减少了。</p>
<h3>22.1.6 <span class="marginalia" data-note="v. 11"><strong>并行两阶段哈希连接</strong></span><span class="hx-absolute -hx-mt-20" id="2216-span-classmarginalia-data-notev-11并行两阶段哈希连接span"></span>
    <a href="#2216-span-classmarginalia-data-notev-11%e5%b9%b6%e8%a1%8c%e4%b8%a4%e9%98%b6%e6%ae%b5%e5%93%88%e5%b8%8c%e8%bf%9e%e6%8e%a5span" class="subheading-anchor" aria-label="Permalink for this section"></a></h3><p>所有并行进程的内存合并起来可能仍然不足以容纳整个哈希表。这可能在计划阶段或稍后在查询执行期间变得明显。在这种情况下，应用的两阶段连接算法与我们迄今为止所看到的有很大不同。</p>
<p>这个算法的主要区别在于它创建了几个较小的哈希表，而不是一个大的哈希表。每个进程都有其自己的表并独立处理自己的批次。(但由于独立的哈希表仍然位于共享内存中，任何进程都可以访问这些表中的任何一个) 如果规划显示将需要不止一个批次，<sup id="fnref:17"><a href="#fn:17" class="footnote-ref" role="doc-noteref">17</a></sup> 那么会立即为每个进程构建一个独立的哈希表。如果决定是在执行阶段做出的，则重建哈希表。<sup id="fnref:18"><a href="#fn:18" class="footnote-ref" role="doc-noteref">18</a></sup></p>
<p>因此，在第一阶段，进程并行扫描内层数据集，将其分成批次并将它们写入临时文件中。<sup id="fnref:19"><a href="#fn:19" class="footnote-ref" role="doc-noteref">19</a></sup> 由于每个进程只读取自己份额的内层数据集，因此它们中的任何一个都不会为任何批次 (即使是第一个批次) 创建完整的哈希表。任何批次的完整数据集仅累积在所有并行进程以同步方式写入的文件中。<sup id="fnref:20"><a href="#fn:20" class="footnote-ref" role="doc-noteref">20</a></sup> 因此，与非并行和一阶段并行版本的算法不同，并行两阶段哈希连接将所有批次写入磁盘，包括第一个批次。</p>
<img src="22-9.png" style="width:70%; float:left" />
<div style="clear:both;"></div>
<p>一旦所有进程都完成了内层数据集的哈希计算，第二阶段就开始了。<sup id="fnref:21"><a href="#fn:21" class="footnote-ref" role="doc-noteref">21</a></sup></p>
<p>如果使用的是非并行版本的算法，属于第一批次的外层数据集将立即与哈希表匹配。但在并行版本的情况下，内存中还没有哈希表，所以工作进程独立处理批次。因此，第二阶段从外层数据集的并行扫描开始，将其分配到批次中，每个批次被写入到一个单独的临时文件中。<sup id="fnref:22"><a href="#fn:22" class="footnote-ref" role="doc-noteref">22</a></sup> 扫描的行不会插入到哈希表中 (就像在第一阶段发生的那样)，因此批次的数量永远不会增加。</p>
<p>一旦所有进程都完成了对外层数据集的扫描，在磁盘上便有了 <em>2N</em> 个临时文件；它们包含内层数据集和外层数据集的批次。</p>
<img src="22-10.png" style="width:70%; float:left" />
<div style="clear:both;"></div>
<p>然后每个进程选择其中一个批次并执行连接：它将内层数据集加载到位于内存中的哈希表中，然后扫描外层数据集，并将它们与哈希表进行匹配。当批次连接完成时，进程选择下一个尚未处理的批次。<sup id="fnref:23"><a href="#fn:23" class="footnote-ref" role="doc-noteref">23</a></sup></p>
<img src="22-11.png" style="width:70%; float:left" />
<div style="clear:both;"></div>
<p>如果没有更多未处理的批次，已完成自己批次的进程开始处理当前正由其他进程处理的批次之一；这种并发处理是可能的，因为所有哈希表都位于共享内存中。</p>
<img src="22-12.png" style="width:70%; float:left" />
<div style="clear:both;"></div>
<p>这种方法比对所有进程使用单个大的哈希表更有效：设置并行处理更容易，且同步的成本更低。</p>
<h3>22.1.7 调整<span class="hx-absolute -hx-mt-20" id="2217-调整"></span>
    <a href="#2217-%e8%b0%83%e6%95%b4" class="subheading-anchor" aria-label="Permalink for this section"></a></h3><p>哈希连接算法支持任何类型的连接：除了内连接，它还可以处理左、右和全外连接，以及半连接和反连接。但是正如我之前提到的，连接条件仅限于等值操作符。</p>
<p>在处理嵌套循环连接时，我们已经观察到其中一些操作。这是右外连接的示例：</p>
<div class="hextra-code-block hx-relative hx-mt-6 first:hx-mt-0 hx-group/code">
  

<div><div class="highlight"><pre tabindex="0" class="chroma"><code class="language-bash" data-lang="bash"><span class="line"><span class="cl"><span class="o">=</span>&gt; EXPLAIN <span class="o">(</span>costs off<span class="o">)</span> SELECT *
</span></span><span class="line"><span class="cl">FROM bookings b
</span></span><span class="line"><span class="cl">  LEFT OUTER JOIN tickets t ON t.book_ref <span class="o">=</span> b.book_ref<span class="p">;</span>
</span></span><span class="line"><span class="cl">               QUERY PLAN
</span></span><span class="line"><span class="cl">−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−
</span></span><span class="line"><span class="cl"> Hash Right Join
</span></span><span class="line"><span class="cl">   Hash Cond: <span class="o">(</span>t.book_ref <span class="o">=</span> b.book_ref<span class="o">)</span>
</span></span><span class="line"><span class="cl">   −&gt; Seq Scan on tickets t
</span></span><span class="line"><span class="cl">   −&gt; Hash
</span></span><span class="line"><span class="cl">       −&gt; Seq Scan on bookings b
</span></span><span class="line"><span class="cl"><span class="o">(</span><span class="m">5</span> rows<span class="o">)</span></span></span></code></pre></div></div><div class="hextra-code-copy-btn-container hx-opacity-0 hx-transition group-hover/code:hx-opacity-100 hx-flex hx-gap-1 hx-absolute hx-m-[11px] hx-right-0 hx-top-0">
  <button
    class="hextra-code-copy-btn hx-group/copybtn hx-transition-all active:hx-opacity-50 hx-bg-primary-700/5 hx-border hx-border-black/5 hx-text-gray-600 hover:hx-text-gray-900 hx-rounded-md hx-p-1.5 dark:hx-bg-primary-300/10 dark:hx-border-white/10 dark:hx-text-gray-400 dark:hover:hx-text-gray-50"
    title="Copy code"
  >
    <div class="copy-icon group-[.copied]/copybtn:hx-hidden hx-pointer-events-none hx-h-4 hx-w-4"></div>
    <div class="success-icon hx-hidden group-[.copied]/copybtn:hx-block hx-pointer-events-none hx-h-4 hx-w-4"></div>
  </button>
</div>

  
</div>
<p>请注意，SQL 查询中指定的逻辑左外连接在执行计划中被转换为右外连接的物理操作。</p>
<p>在逻辑层面，bookings 是外表 (构成连接操作的左侧)，而 tickets 表是内表。因此，没有 tickets 的 bookings 也必须包含在连接结果中。</p>
<p>在物理层面，内层数据集和外层数据集是基于连接成本而非它们在查询文本中的位置来分配的。通常情况下，这意味着拥有较小哈希表的数据集将被用作为内层数据集。这正是在此处发生的事情：bookings 表是内层数据集，左外连接被改为右外连接。</p>
<p>反之亦然，如果查询指定右外连接 (以显示不与任何 bookings 相关的 tickets)，那么执行计划使用左外连接：</p>
<div class="hextra-code-block hx-relative hx-mt-6 first:hx-mt-0 hx-group/code">
  

<div><div class="highlight"><pre tabindex="0" class="chroma"><code class="language-bash" data-lang="bash"><span class="line"><span class="cl"><span class="o">=</span>&gt; EXPLAIN <span class="o">(</span>costs off<span class="o">)</span> SELECT *
</span></span><span class="line"><span class="cl">FROM bookings b
</span></span><span class="line"><span class="cl">  RIGHT OUTER JOIN tickets t ON t.book_ref <span class="o">=</span> b.book_ref<span class="p">;</span>
</span></span><span class="line"><span class="cl">               QUERY PLAN
</span></span><span class="line"><span class="cl">−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−
</span></span><span class="line"><span class="cl"> Hash Left Join
</span></span><span class="line"><span class="cl">   Hash Cond: <span class="o">(</span>t.book_ref <span class="o">=</span> b.book_ref<span class="o">)</span>
</span></span><span class="line"><span class="cl">   −&gt; Seq Scan on tickets t
</span></span><span class="line"><span class="cl">   −&gt; Hash
</span></span><span class="line"><span class="cl">       −&gt; Seq Scan on bookings b
</span></span><span class="line"><span class="cl"><span class="o">(</span><span class="m">5</span> rows<span class="o">)</span></span></span></code></pre></div></div><div class="hextra-code-copy-btn-container hx-opacity-0 hx-transition group-hover/code:hx-opacity-100 hx-flex hx-gap-1 hx-absolute hx-m-[11px] hx-right-0 hx-top-0">
  <button
    class="hextra-code-copy-btn hx-group/copybtn hx-transition-all active:hx-opacity-50 hx-bg-primary-700/5 hx-border hx-border-black/5 hx-text-gray-600 hover:hx-text-gray-900 hx-rounded-md hx-p-1.5 dark:hx-bg-primary-300/10 dark:hx-border-white/10 dark:hx-text-gray-400 dark:hover:hx-text-gray-50"
    title="Copy code"
  >
    <div class="copy-icon group-[.copied]/copybtn:hx-hidden hx-pointer-events-none hx-h-4 hx-w-4"></div>
    <div class="success-icon hx-hidden group-[.copied]/copybtn:hx-block hx-pointer-events-none hx-h-4 hx-w-4"></div>
  </button>
</div>

  
</div>
<p>为了完整地展示，我将提供一个包含全外连接的查询计划示例：</p>
<div class="hextra-code-block hx-relative hx-mt-6 first:hx-mt-0 hx-group/code">
  

<div><div class="highlight"><pre tabindex="0" class="chroma"><code class="language-bash" data-lang="bash"><span class="line"><span class="cl"><span class="o">=</span>&gt; EXPLAIN <span class="o">(</span>costs off<span class="o">)</span> SELECT *
</span></span><span class="line"><span class="cl">FROM bookings b
</span></span><span class="line"><span class="cl">  FULL OUTER JOIN tickets t ON t.book_ref <span class="o">=</span> b.book_ref<span class="p">;</span>
</span></span><span class="line"><span class="cl">               QUERY PLAN
</span></span><span class="line"><span class="cl">−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−
</span></span><span class="line"><span class="cl"> Hash Full Join
</span></span><span class="line"><span class="cl">   Hash Cond: <span class="o">(</span>t.book_ref <span class="o">=</span> b.book_ref<span class="o">)</span>
</span></span><span class="line"><span class="cl">   −&gt; Seq Scan on tickets t
</span></span><span class="line"><span class="cl">   −&gt; Hash
</span></span><span class="line"><span class="cl">       −&gt; Seq Scan on bookings b
</span></span><span class="line"><span class="cl"><span class="o">(</span><span class="m">5</span> rows<span class="o">)</span></span></span></code></pre></div></div><div class="hextra-code-copy-btn-container hx-opacity-0 hx-transition group-hover/code:hx-opacity-100 hx-flex hx-gap-1 hx-absolute hx-m-[11px] hx-right-0 hx-top-0">
  <button
    class="hextra-code-copy-btn hx-group/copybtn hx-transition-all active:hx-opacity-50 hx-bg-primary-700/5 hx-border hx-border-black/5 hx-text-gray-600 hover:hx-text-gray-900 hx-rounded-md hx-p-1.5 dark:hx-bg-primary-300/10 dark:hx-border-white/10 dark:hx-text-gray-400 dark:hover:hx-text-gray-50"
    title="Copy code"
  >
    <div class="copy-icon group-[.copied]/copybtn:hx-hidden hx-pointer-events-none hx-h-4 hx-w-4"></div>
    <div class="success-icon hx-hidden group-[.copied]/copybtn:hx-block hx-pointer-events-none hx-h-4 hx-w-4"></div>
  </button>
</div>

  
</div>
<p>目前，并行哈希连接不支持右外连接和全外连接。<sup id="fnref:24"><a href="#fn:24" class="footnote-ref" role="doc-noteref">24</a></sup></p>
<p>请注意，下一个示例使用 bookings 表作为外层数据集，但如果支持的话，规划器会更倾向于使用右外连接。</p>
<div class="hextra-code-block hx-relative hx-mt-6 first:hx-mt-0 hx-group/code">
  

<div><div class="highlight"><pre tabindex="0" class="chroma"><code class="language-bash" data-lang="bash"><span class="line"><span class="cl"><span class="o">=</span>&gt; EXPLAIN <span class="o">(</span>costs off<span class="o">)</span> SELECT sum<span class="o">(</span>b.total_amount<span class="o">)</span>
</span></span><span class="line"><span class="cl">FROM bookings b
</span></span><span class="line"><span class="cl">  LEFT OUTER JOIN tickets t ON t.book_ref <span class="o">=</span> b.book_ref<span class="p">;</span>
</span></span><span class="line"><span class="cl">                             QUERY PLAN
</span></span><span class="line"><span class="cl">−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−
</span></span><span class="line"><span class="cl"> Finalize Aggregate
</span></span><span class="line"><span class="cl">   −&gt; Gather
</span></span><span class="line"><span class="cl">      Workers Planned: <span class="m">2</span>
</span></span><span class="line"><span class="cl">      −&gt; Partial Aggregate
</span></span><span class="line"><span class="cl">          −&gt; Parallel Hash Left Join
</span></span><span class="line"><span class="cl">              Hash Cond: <span class="o">(</span>b.book_ref <span class="o">=</span> t.book_ref<span class="o">)</span>
</span></span><span class="line"><span class="cl">              −&gt; Parallel Seq Scan on bookings b
</span></span><span class="line"><span class="cl">              −&gt; Parallel Hash
</span></span><span class="line"><span class="cl">                  −&gt; Parallel Index Only Scan using tickets_book...
</span></span><span class="line"><span class="cl"><span class="o">(</span><span class="m">9</span> rows<span class="o">)</span></span></span></code></pre></div></div><div class="hextra-code-copy-btn-container hx-opacity-0 hx-transition group-hover/code:hx-opacity-100 hx-flex hx-gap-1 hx-absolute hx-m-[11px] hx-right-0 hx-top-0">
  <button
    class="hextra-code-copy-btn hx-group/copybtn hx-transition-all active:hx-opacity-50 hx-bg-primary-700/5 hx-border hx-border-black/5 hx-text-gray-600 hover:hx-text-gray-900 hx-rounded-md hx-p-1.5 dark:hx-bg-primary-300/10 dark:hx-border-white/10 dark:hx-text-gray-400 dark:hover:hx-text-gray-50"
    title="Copy code"
  >
    <div class="copy-icon group-[.copied]/copybtn:hx-hidden hx-pointer-events-none hx-h-4 hx-w-4"></div>
    <div class="success-icon hx-hidden group-[.copied]/copybtn:hx-block hx-pointer-events-none hx-h-4 hx-w-4"></div>
  </button>
</div>

  
</div>
<h2>22.2 非重复值与分组<span class="hx-absolute -hx-mt-20" id="222-非重复值与分组"></span>
    <a href="#222-%e9%9d%9e%e9%87%8d%e5%a4%8d%e5%80%bc%e4%b8%8e%e5%88%86%e7%bb%84" class="subheading-anchor" aria-label="Permalink for this section"></a></h2><p>用于聚合分组和去重的算法与连接算法非常相似。它们可使用的一种方法是在所需列上构建哈希表。只有当哈希表中尚不存在这些值时，这些值才被加入到哈希表中。结果是，哈希表累积了所有不同的值。</p>
<p>执行哈希聚合的节点称为 HashAggregate。<sup id="fnref:25"><a href="#fn:25" class="footnote-ref" role="doc-noteref">25</a></sup></p>
<p>让我们考虑一些可能需要这个节点的情况。</p>
<p>每个旅行类别的座位数 (GROUP BY)：</p>
<div class="hextra-code-block hx-relative hx-mt-6 first:hx-mt-0 hx-group/code">
  

<div><div class="highlight"><pre tabindex="0" class="chroma"><code class="language-bash" data-lang="bash"><span class="line"><span class="cl"><span class="o">=</span>&gt; EXPLAIN <span class="o">(</span>costs off<span class="o">)</span> SELECT fare_conditions, count<span class="o">(</span>*<span class="o">)</span>
</span></span><span class="line"><span class="cl">FROM seats
</span></span><span class="line"><span class="cl">GROUP BY fare_conditions<span class="p">;</span>
</span></span><span class="line"><span class="cl">          QUERY PLAN
</span></span><span class="line"><span class="cl">−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−
</span></span><span class="line"><span class="cl"> HashAggregate
</span></span><span class="line"><span class="cl">   Group Key: fare_conditions
</span></span><span class="line"><span class="cl">   −&gt; Seq Scan on seats
</span></span><span class="line"><span class="cl"><span class="o">(</span><span class="m">3</span> rows<span class="o">)</span></span></span></code></pre></div></div><div class="hextra-code-copy-btn-container hx-opacity-0 hx-transition group-hover/code:hx-opacity-100 hx-flex hx-gap-1 hx-absolute hx-m-[11px] hx-right-0 hx-top-0">
  <button
    class="hextra-code-copy-btn hx-group/copybtn hx-transition-all active:hx-opacity-50 hx-bg-primary-700/5 hx-border hx-border-black/5 hx-text-gray-600 hover:hx-text-gray-900 hx-rounded-md hx-p-1.5 dark:hx-bg-primary-300/10 dark:hx-border-white/10 dark:hx-text-gray-400 dark:hover:hx-text-gray-50"
    title="Copy code"
  >
    <div class="copy-icon group-[.copied]/copybtn:hx-hidden hx-pointer-events-none hx-h-4 hx-w-4"></div>
    <div class="success-icon hx-hidden group-[.copied]/copybtn:hx-block hx-pointer-events-none hx-h-4 hx-w-4"></div>
  </button>
</div>

  
</div>
<p>旅行类别列表 (DISTINCT）：</p>
<div class="hextra-code-block hx-relative hx-mt-6 first:hx-mt-0 hx-group/code">
  

<div><div class="highlight"><pre tabindex="0" class="chroma"><code class="language-bash" data-lang="bash"><span class="line"><span class="cl"><span class="o">=</span>&gt; EXPLAIN <span class="o">(</span>costs off<span class="o">)</span> SELECT DISTINCT fare_conditions
</span></span><span class="line"><span class="cl">FROM seats<span class="p">;</span>
</span></span><span class="line"><span class="cl">          QUERY PLAN
</span></span><span class="line"><span class="cl">−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−
</span></span><span class="line"><span class="cl"> HashAggregate
</span></span><span class="line"><span class="cl">   Group Key: fare_conditions
</span></span><span class="line"><span class="cl">   −&gt; Seq Scan on seats
</span></span><span class="line"><span class="cl"><span class="o">(</span><span class="m">3</span> rows<span class="o">)</span></span></span></code></pre></div></div><div class="hextra-code-copy-btn-container hx-opacity-0 hx-transition group-hover/code:hx-opacity-100 hx-flex hx-gap-1 hx-absolute hx-m-[11px] hx-right-0 hx-top-0">
  <button
    class="hextra-code-copy-btn hx-group/copybtn hx-transition-all active:hx-opacity-50 hx-bg-primary-700/5 hx-border hx-border-black/5 hx-text-gray-600 hover:hx-text-gray-900 hx-rounded-md hx-p-1.5 dark:hx-bg-primary-300/10 dark:hx-border-white/10 dark:hx-text-gray-400 dark:hover:hx-text-gray-50"
    title="Copy code"
  >
    <div class="copy-icon group-[.copied]/copybtn:hx-hidden hx-pointer-events-none hx-h-4 hx-w-4"></div>
    <div class="success-icon hx-hidden group-[.copied]/copybtn:hx-block hx-pointer-events-none hx-h-4 hx-w-4"></div>
  </button>
</div>

  
</div>
<p>旅行类别再加上一个值 (UNION)：</p>
<div class="hextra-code-block hx-relative hx-mt-6 first:hx-mt-0 hx-group/code">
  

<div><div class="highlight"><pre tabindex="0" class="chroma"><code class="language-bash" data-lang="bash"><span class="line"><span class="cl"><span class="o">=</span>&gt; EXPLAIN <span class="o">(</span>costs off<span class="o">)</span> SELECT fare_conditions
</span></span><span class="line"><span class="cl">FROM seats
</span></span><span class="line"><span class="cl">UNION
</span></span><span class="line"><span class="cl">SELECT NULL<span class="p">;</span>
</span></span><span class="line"><span class="cl">             QUERY PLAN
</span></span><span class="line"><span class="cl">−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−
</span></span><span class="line"><span class="cl"> HashAggregate
</span></span><span class="line"><span class="cl">   Group Key: seats.fare_conditions
</span></span><span class="line"><span class="cl">   −&gt; Append
</span></span><span class="line"><span class="cl">       −&gt; Seq Scan on seats
</span></span><span class="line"><span class="cl">       −&gt; Result
</span></span><span class="line"><span class="cl"><span class="o">(</span><span class="m">5</span> rows<span class="o">)</span></span></span></code></pre></div></div><div class="hextra-code-copy-btn-container hx-opacity-0 hx-transition group-hover/code:hx-opacity-100 hx-flex hx-gap-1 hx-absolute hx-m-[11px] hx-right-0 hx-top-0">
  <button
    class="hextra-code-copy-btn hx-group/copybtn hx-transition-all active:hx-opacity-50 hx-bg-primary-700/5 hx-border hx-border-black/5 hx-text-gray-600 hover:hx-text-gray-900 hx-rounded-md hx-p-1.5 dark:hx-bg-primary-300/10 dark:hx-border-white/10 dark:hx-text-gray-400 dark:hover:hx-text-gray-50"
    title="Copy code"
  >
    <div class="copy-icon group-[.copied]/copybtn:hx-hidden hx-pointer-events-none hx-h-4 hx-w-4"></div>
    <div class="success-icon hx-hidden group-[.copied]/copybtn:hx-block hx-pointer-events-none hx-h-4 hx-w-4"></div>
  </button>
</div>

  
</div>
<p>Append 节点将两个数据集组合在一起，但不会删除任何重复项，这些重复项不应出现在 UNION 结果中。它们必须由 HashAggregate 节点单独删除。</p>
<p>为哈希表分配的内存块受到 <span class="marginalia" data-note="4MB"><em>work_mem</em></span> × <span class="marginalia" data-note="1.0"><em>hash_mem_multiplier</em></span> 的限制，就像哈希连接一样。</p>
<p>如果哈希表适合分配的内存，那么聚合使用单个批次：</p>
<div class="hextra-code-block hx-relative hx-mt-6 first:hx-mt-0 hx-group/code">
  

<div><div class="highlight"><pre tabindex="0" class="chroma"><code class="language-bash" data-lang="bash"><span class="line"><span class="cl"><span class="o">=</span>&gt; EXPLAIN <span class="o">(</span>analyze, costs off, timing off, summary off<span class="o">)</span>
</span></span><span class="line"><span class="cl">SELECT DISTINCT amount FROM ticket_flights<span class="p">;</span>
</span></span><span class="line"><span class="cl">                          QUERY PLAN
</span></span><span class="line"><span class="cl">−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−
</span></span><span class="line"><span class="cl"> HashAggregate <span class="o">(</span>actual <span class="nv">rows</span><span class="o">=</span><span class="m">338</span> <span class="nv">loops</span><span class="o">=</span>1<span class="o">)</span>
</span></span><span class="line"><span class="cl">   Group Key: amount
</span></span><span class="line"><span class="cl">   Batches: <span class="m">1</span> Memory Usage: 61kB
</span></span><span class="line"><span class="cl">   −&gt; Seq Scan on ticket_flights <span class="o">(</span>actual <span class="nv">rows</span><span class="o">=</span><span class="m">8391852</span> <span class="nv">loops</span><span class="o">=</span>1<span class="o">)</span>
</span></span><span class="line"><span class="cl"><span class="o">(</span><span class="m">4</span> rows<span class="o">)</span></span></span></code></pre></div></div><div class="hextra-code-copy-btn-container hx-opacity-0 hx-transition group-hover/code:hx-opacity-100 hx-flex hx-gap-1 hx-absolute hx-m-[11px] hx-right-0 hx-top-0">
  <button
    class="hextra-code-copy-btn hx-group/copybtn hx-transition-all active:hx-opacity-50 hx-bg-primary-700/5 hx-border hx-border-black/5 hx-text-gray-600 hover:hx-text-gray-900 hx-rounded-md hx-p-1.5 dark:hx-bg-primary-300/10 dark:hx-border-white/10 dark:hx-text-gray-400 dark:hover:hx-text-gray-50"
    title="Copy code"
  >
    <div class="copy-icon group-[.copied]/copybtn:hx-hidden hx-pointer-events-none hx-h-4 hx-w-4"></div>
    <div class="success-icon hx-hidden group-[.copied]/copybtn:hx-block hx-pointer-events-none hx-h-4 hx-w-4"></div>
  </button>
</div>

  
</div>
<p>在 amounts 字段中没有那么多不同的值，所以哈希表只占用了 61 kB (Memory Usage)。</p>
<p>一旦哈希表填满了所分配的内存，所有后续值都会<span class="marginalia" data-note="v. 13">溢出到临时文件中</span>，并基于其哈希值的若干位进行分区。分区数量是 2 的幂，选择分区的方式是使每个分区的哈希表都适合所分配的内存。当然，评估的准确性取决于所收集的统计信息的质量，因此接收到的数字乘以 1.5，以进一步减小分区大小并提高每个分区一阶段处理完成的机会。<sup id="fnref:26"><a href="#fn:26" class="footnote-ref" role="doc-noteref">26</a></sup></p>
<p>一旦整个数据集被扫描完毕，节点返回那些成功进入哈希表的值的聚合结果。</p>
<p>然后清除哈希表，上一阶段保存到临时文件中的每个分区被扫描和处理，就像任何其他数据集一样。如果哈希表仍然超出分配的内存，将要溢出的行将再次被分区并写入磁盘以供进一步处理。</p>
<p>为了避免过多 I/O，两阶段哈希连接算法将 MCVS 移入第一批次。然而，聚合不需要这种优化：适合分配内存的那些行不会被分成分区，并且 MCVS 很可能在足够早的时候出现，从而进入 RAM。</p>
<div class="hextra-code-block hx-relative hx-mt-6 first:hx-mt-0 hx-group/code">
  

<div><div class="highlight"><pre tabindex="0" class="chroma"><code class="language-bash" data-lang="bash"><span class="line"><span class="cl"><span class="o">=</span>&gt; EXPLAIN <span class="o">(</span>analyze, costs off, timing off, summary off<span class="o">)</span>
</span></span><span class="line"><span class="cl">SELECT DISTINCT flight_id FROM ticket_flights<span class="p">;</span>
</span></span><span class="line"><span class="cl">                          QUERY PLAN
</span></span><span class="line"><span class="cl">−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−
</span></span><span class="line"><span class="cl"> HashAggregate <span class="o">(</span>actual <span class="nv">rows</span><span class="o">=</span><span class="m">150588</span> <span class="nv">loops</span><span class="o">=</span>1<span class="o">)</span>
</span></span><span class="line"><span class="cl">   Group Key: flight_id
</span></span><span class="line"><span class="cl">   Batches: <span class="m">5</span> Memory Usage: 4145kB Disk Usage: 98184kB
</span></span><span class="line"><span class="cl">   −&gt; Seq Scan on ticket_flights <span class="o">(</span>actual <span class="nv">rows</span><span class="o">=</span><span class="m">8391852</span> <span class="nv">loops</span><span class="o">=</span>1<span class="o">)</span>
</span></span><span class="line"><span class="cl"><span class="o">(</span><span class="m">4</span> rows<span class="o">)</span></span></span></code></pre></div></div><div class="hextra-code-copy-btn-container hx-opacity-0 hx-transition group-hover/code:hx-opacity-100 hx-flex hx-gap-1 hx-absolute hx-m-[11px] hx-right-0 hx-top-0">
  <button
    class="hextra-code-copy-btn hx-group/copybtn hx-transition-all active:hx-opacity-50 hx-bg-primary-700/5 hx-border hx-border-black/5 hx-text-gray-600 hover:hx-text-gray-900 hx-rounded-md hx-p-1.5 dark:hx-bg-primary-300/10 dark:hx-border-white/10 dark:hx-text-gray-400 dark:hover:hx-text-gray-50"
    title="Copy code"
  >
    <div class="copy-icon group-[.copied]/copybtn:hx-hidden hx-pointer-events-none hx-h-4 hx-w-4"></div>
    <div class="success-icon hx-hidden group-[.copied]/copybtn:hx-block hx-pointer-events-none hx-h-4 hx-w-4"></div>
  </button>
</div>

  
</div>
<p>在这个例子中，不同 ID 的数量相对较多，所以哈希表不适合所分配的内存。执行查询使用了五个批次：一个批次用于初始数据集，四个批次用于写入磁盘的分区。</p>
<div class="footnotes" role="doc-endnotes">
<hr>
<ol>
<li id="fn:1">
<p>backend/executor/nodeHashjoin.c&#160;<a href="#fnref:1" class="footnote-backref" role="doc-backlink">&#x21a9;&#xfe0e;</a></p>
</li>
<li id="fn:2">
<p>backend/executor/nodeHash.c&#160;<a href="#fnref:2" class="footnote-backref" role="doc-backlink">&#x21a9;&#xfe0e;</a></p>
</li>
<li id="fn:3">
<p>backend/utils/hash/dynahash.c&#160;<a href="#fnref:3" class="footnote-backref" role="doc-backlink">&#x21a9;&#xfe0e;</a></p>
</li>
<li id="fn:4">
<p>backend/executor/nodeHash.c, ExecChooseHashTableSize function&#160;<a href="#fnref:4" class="footnote-backref" role="doc-backlink">&#x21a9;&#xfe0e;</a></p>
</li>
<li id="fn:5">
<p>backend/optimizer/plan/createplan.c, create_hashjoin_plan function&#160;<a href="#fnref:5" class="footnote-backref" role="doc-backlink">&#x21a9;&#xfe0e;</a></p>
</li>
<li id="fn:6">
<p>backend/optimizer/path/costsize.c, initial_cost_hashjoin and final_cost_hashjoin functions&#160;<a href="#fnref:6" class="footnote-backref" role="doc-backlink">&#x21a9;&#xfe0e;</a></p>
</li>
<li id="fn:7">
<p>backend/utils/adt/selfuncs.c, estimate_hash_bucket_stats function&#160;<a href="#fnref:7" class="footnote-backref" role="doc-backlink">&#x21a9;&#xfe0e;</a></p>
</li>
<li id="fn:8">
<p>backend/executor/nodeHash.c, ExecHashGetBucketAndBatch function&#160;<a href="#fnref:8" class="footnote-backref" role="doc-backlink">&#x21a9;&#xfe0e;</a></p>
</li>
<li id="fn:9">
<p>backend/executor/nodeHash.c, ExecChooseHashTableSize function&#160;<a href="#fnref:9" class="footnote-backref" role="doc-backlink">&#x21a9;&#xfe0e;</a></p>
</li>
<li id="fn:10">
<p>backend/executor/nodeHash.c, ExecHashTableInsert function&#160;<a href="#fnref:10" class="footnote-backref" role="doc-backlink">&#x21a9;&#xfe0e;</a></p>
</li>
<li id="fn:11">
<p>backend/executor/nodeHash.c, ExecHashBuildSkewHash function&#160;<a href="#fnref:11" class="footnote-backref" role="doc-backlink">&#x21a9;&#xfe0e;</a></p>
</li>
<li id="fn:12">
<p>backend/executor/nodeHash.c, ExecHashIncreaseNumBatches function&#160;<a href="#fnref:12" class="footnote-backref" role="doc-backlink">&#x21a9;&#xfe0e;</a></p>
</li>
<li id="fn:13">
<p>backend/optimizer/path/costsize.c, page_size function&#160;<a href="#fnref:13" class="footnote-backref" role="doc-backlink">&#x21a9;&#xfe0e;</a></p>
</li>
<li id="fn:14">
<p>backend/executor/nodeHash.c, MultiExecParallelHash function&#160;<a href="#fnref:14" class="footnote-backref" role="doc-backlink">&#x21a9;&#xfe0e;</a></p>
</li>
<li id="fn:15">
<p>backend/storage/ipc/barrier.c&#160;<a href="#fnref:15" class="footnote-backref" role="doc-backlink">&#x21a9;&#xfe0e;</a></p>
</li>
<li id="fn:16">
<p>backend/executor/nodeHashjoin.c, ExecParallelHashJoin function&#160;<a href="#fnref:16" class="footnote-backref" role="doc-backlink">&#x21a9;&#xfe0e;</a></p>
</li>
<li id="fn:17">
<p>backend/executor/nodeHash.c, ExecChooseHashTableSize function&#160;<a href="#fnref:17" class="footnote-backref" role="doc-backlink">&#x21a9;&#xfe0e;</a></p>
</li>
<li id="fn:18">
<p>backend/executor/nodeHash.c, ExecParallelHashIncreaseNumBatches function&#160;<a href="#fnref:18" class="footnote-backref" role="doc-backlink">&#x21a9;&#xfe0e;</a></p>
</li>
<li id="fn:19">
<p>backend/executor/nodeHash.c, MultiExecParallelHash function&#160;<a href="#fnref:19" class="footnote-backref" role="doc-backlink">&#x21a9;&#xfe0e;</a></p>
</li>
<li id="fn:20">
<p>backend/utils/sort/sharedtuplestore.c&#160;<a href="#fnref:20" class="footnote-backref" role="doc-backlink">&#x21a9;&#xfe0e;</a></p>
</li>
<li id="fn:21">
<p>backend/executor/nodeHashjoin.c, ExecParallelHashJoin function&#160;<a href="#fnref:21" class="footnote-backref" role="doc-backlink">&#x21a9;&#xfe0e;</a></p>
</li>
<li id="fn:22">
<p>backend/executor/nodeHashjoin.c, ExecParallelHashJoinPartitionOuter function&#160;<a href="#fnref:22" class="footnote-backref" role="doc-backlink">&#x21a9;&#xfe0e;</a></p>
</li>
<li id="fn:23">
<p>backend/executor/nodeHashjoin.c, ExecParallelHashJoinNewBatch function&#160;<a href="#fnref:23" class="footnote-backref" role="doc-backlink">&#x21a9;&#xfe0e;</a></p>
</li>
<li id="fn:24">
<p>commitfest.postgresql.org/33/2903&#160;<a href="#fnref:24" class="footnote-backref" role="doc-backlink">&#x21a9;&#xfe0e;</a></p>
</li>
<li id="fn:25">
<p>backend/executor/nodeAgg.c&#160;<a href="#fnref:25" class="footnote-backref" role="doc-backlink">&#x21a9;&#xfe0e;</a></p>
</li>
<li id="fn:26">
<p>backend/executor/nodeAgg.c, hash_choose_num_partitions function&#160;<a href="#fnref:26" class="footnote-backref" role="doc-backlink">&#x21a9;&#xfe0e;</a></p>
</li>
</ol>
</div>

        </div>
        <div class="hx-mt-16"></div>
        
        
      </main>
    </article>
  </div>

      <footer class="hextra-footer hx-bg-gray-100 hx-pb-[env(safe-area-inset-bottom)] dark:hx-bg-neutral-900 print:hx-bg-transparent"><div
    class="hx-max-w-screen-xl hx-mx-auto hx-flex hx-justify-center hx-py-12 hx-pl-[max(env(safe-area-inset-left),1.5rem)] hx-pr-[max(env(safe-area-inset-right),1.5rem)] hx-text-gray-600 dark:hx-text-gray-400 md:hx-justify-start"
  >
    <div class="hx-flex hx-w-full hx-flex-col hx-items-center sm:hx-items-start"><div class="hx-mt-6 hx-text-xs">本中文译文版权归熊灿灿所有。英文原文版权归 Postgres Professional 所有。本译文根据授权翻译与发布。</div>
    </div>
  </div>
</footer>
    
    <script defer src="/js/main.js" integrity=""></script>


<script defer src="/lib/flexsearch/flexsearch.bundle.min.0425860527cc9968f9f049421c7a56b39327d475e2e3a8f550416be3a9134327.js" integrity="sha256-BCWGBSfMmWj58ElCHHpWs5Mn1HXi46j1UEFr46kTQyc="></script>
    <script defer src="/en.search.js" integrity=""></script>
<link type="text/css" rel="stylesheet" href="/lib/katex/katex.min.7e5db7914b97e596a36c1abb67ccc7f174f8bb71d38c9a88c55b262ed1737f97.css" integrity="sha256-fl23kUuX5ZajbBq7Z8zH8XT4u3HTjJqIxVsmLtFzf5c=" />
  <script defer src="/lib/katex/katex.min.9f45307c5794ed247a0d095f3a62e52ef2215a67b2327203a7fd919959ae79d1.js" integrity="sha256-n0UwfFeU7SR6DQlfOmLlLvIhWmeyMnIDp/2RmVmuedE="></script>
  <script defer src="/lib/katex/auto-render.min.7b57d427ac6270677daf8d8380ded2cc73336f9149a167b8e1fe0d6ef66604ae.js" integrity="sha256-e1fUJ6xicGd9r42DgN7SzHMzb5FJoWe44f4NbvZmBK4="></script>
  <script defer src="/lib/katex/mhchem.min.f0ca03df194b8c3d6017ff455db6a0ef98857905663fa311a6cded788b15340b.js" integrity="sha256-8MoD3xlLjD1gF/9FXbag75iFeQVmP6MRps3teIsVNAs="></script>
  <script>
    
    
    document.addEventListener("DOMContentLoaded", function () {
      renderMathInElement(document.body, {
        delimiters: [
          { left: "$$", right: "$$", display: true },
          { left: "$", right: "$", display: false },
          { left: "\\(", right: "\\)", display: false },
          { left: "\\begin{equation}", right: "\\end{equation}", display: true },
          {left: "\\begin{align}", right: "\\end{align}", display: true},
          {left: "\\begin{alignat}", right: "\\end{alignat}", display: true},
          {left: "\\begin{gather}", right: "\\end{gather}", display: true},
          {left: "\\begin{CD}", right: "\\end{CD}", display: true},
          { left: "\\[", right: "\\]", display: true },
        ],
        throwOnError: false,
      });
    });
  </script>


  </body>
</html>
